excel button position

[homeguard]

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.

 

[SkipVought]

Hi,

Check out the Excel Object Model. There is no BUTTONS collection

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

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[homeguard]

hmm that worked but now im getting "type mismatch" message its still highlighting the same line.

 

[SkipVought]

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

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[homeguard]

i think whats the difference?

 

[SkipVought]

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,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[homeguard]

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.

 

[SkipVought]

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"...

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,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[homeguard]

hmm ok, thank you for all your help