Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

excel button position

Status
Not open for further replies.

homeguard

IS-IT--Management
Jul 12, 2007
47
US
Hello All,

I am trying to get the position of a certain button on my excel sheet here is the code im using:

Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell

MsgBox r.Row 'row number for the button
MsgBox r.Value 'its value

It keep telling me "unable to get button property of the worksheet class" and highlights the "Set r" line.

What am i doing wrong?

Thanks.
 




Hi,

Check out the Excel Object Model. There is no BUTTONS collection
Code:
 Dim r As Range
 Set r = ActiveSheet.Shapes(Application.Caller).TopLeftCell


Skip,

[glasses] When a wee mystic is on the loose..
It's a Small Medium at Large! [tongue]
 
hmm that worked but now im getting "type mismatch" message its still highlighting the same line.
 



Are you using a Control Toolbox button rather than a Forms button?

Skip,

[glasses] When a wee mystic is on the loose..
It's a Small Medium at Large! [tongue]
 




Control Toolbar controls are more robust but are more diffucult to manipulate.

You obviously have many buttons on your sheet. What are your requirements for these controls?

Skip,

[glasses] When a wee mystic is on the loose..
It's a Small Medium at Large! [tongue]
 
well basically im making a sheet that has a list of tasks with due dates, i am wanting to add buttons to every row to allow the user to put it on his/her outlook calendar. I think i have the code to do this but im not quite there yet. I just need to link up the specific column/row with the outlook code.

let me know if you've seen an example on how to do this too.

Thanks tons.
 




You could just as easliy format a cell to appear like a button and use the Woksheet_SelectionChange event.

So if A5 to A25 were the "buttons"...
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A5:A25")) Is Nothing Then
        MsgBox "the row is " & Target.Row
    End If
End Sub


Skip,

[glasses] When a wee mystic is on the loose..
It's a Small Medium at Large! [tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top