Can I get Button name or Cell Reference

T

Trefor

If I have a sheet with 20 buttons (1 on each row) and each button points to
the same macro, is it possible for the macro to determine the Row number (Or
possibly the Button Name)?

Based on the row, the macro will continue updating data on that Row.
 
D

Dave Peterson

This is a button from the Forms toolbar?

Option Explicit
Sub testme()

Dim BTN As Button

Set BTN = ActiveSheet.Buttons(Application.Caller)

MsgBox Application.Caller & vbLf _
& BTN.Name & vbLf _
& BTN.TopLeftCell.Address(0, 0) & vbLf _
& BTN.Caption

End Sub


Application.caller will give you the name of the button.
(BTN.Name is redundant--but sometimes easier to type <bg>.)
 
K

Ken Johnson

Trefor,
If it's a button on a worksheet from the forms toolbar then
Application.Caller returns the button's name. If you want to identify
the button using its caption eg "Start" then try:
Dim WhichButton as Shape
Set WhichButton = ActiveSheet.Shapes(Application.Caller)
If WhichButton.TextFrame.Characters.Text = "Start" then do something..
Other than that I really don't know. I mainly work on a Mac so I know
nothing about ActiveX controls.
Hope this isn useful.
Ken Johnson
 
Top