Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Row or Button Name
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. -- -- Trefor |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Row or Button Name
Hi Trefor,
Try: '==============. Sub Tester() MsgBox Application.Caller MsgBox ActiveSheet.Buttons(Application.Caller). _ TopLeftCell.Address End Sub '<<============== --- Regards, Norman "Trefor" wrote in message ... 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. -- -- Trefor |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Row or Button Name
Hi Trefor,
Or, to return the row (as you asked) rather than the address: '============== Sub Tester() MsgBox Application.Caller MsgBox ActiveSheet.Buttons(Application.Caller). _ TopLeftCell.Row End Sub '<<============== --- Regards, Norman "Trefor" wrote in message ... 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. -- -- Trefor |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Row or Button Name
Norman,
I was close, but not close enough. This is Perfect, many thanks. -- Trefor "Norman Jones" wrote: Hi Trefor, Or, to return the row (as you asked) rather than the address: '============== Sub Tester() MsgBox Application.Caller MsgBox ActiveSheet.Buttons(Application.Caller). _ TopLeftCell.Row End Sub '<<============== --- Regards, Norman "Trefor" wrote in message ... 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. -- -- Trefor |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Row or Button Name
"Norman Jones" wrote in message
... Hello Norman, How can I find Button12 among a lot of them distributed over a lot of sheets in the same workbook? I happen sometimes to becom crazy on that... Ciao Bruno |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Row or Button Name
Ciao Bruno,
'=========== Sub Tester2() 'To select the button ActiveSheet.Buttons("Button12").Select 'or to return the address MsgBox ActiveSheet.Buttons("Button12"). _ TopLeftCell.Address End Sub '<<=========== --- Regards, Norman "Bruno Campanini" wrote in message ... "Norman Jones" wrote in message ... Hello Norman, How can I find Button12 among a lot of them distributed over a lot of sheets in the same workbook? I happen sometimes to becom crazy on that... Ciao Bruno |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Row or Button Name
Hi Bruno,
Re-reading your question, try: '============ Sub Tester3() Dim Rng As Range Dim WB As Workbook Dim SH As Worksheet Dim btn As Button Const sStr As String = "Button 12" '<<==== CHANGE Dim StrAddress As String Set WB = ActiveWorkbook '<<======= CHANGE For Each SH In WB.Worksheets On Error Resume Next Set btn = SH.Buttons(sStr) On Error GoTo 0 If Not btn Is Nothing Then StrAddress = _ btn.TopLeftCell.Address(external:=True) Exit For End If Next SH If Not btn Is Nothing Then MsgBox StrAddress End Sub '<<============ --- Regards, Norman "Bruno Campanini" wrote in message ... "Norman Jones" wrote in message ... Hello Norman, How can I find Button12 among a lot of them distributed over a lot of sheets in the same workbook? I happen sometimes to becom crazy on that... Ciao Bruno |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Row or Button Name
"Norman Jones" wrote in message
... Hi Bruno, Re-reading your question, try: '============ Sub Tester3() Dim Rng As Range Dim WB As Workbook Dim SH As Worksheet Dim btn As Button Const sStr As String = "Button 12" '<<==== CHANGE Dim StrAddress As String Set WB = ActiveWorkbook '<<======= CHANGE For Each SH In WB.Worksheets On Error Resume Next Set btn = SH.Buttons(sStr) On Error GoTo 0 If Not btn Is Nothing Then StrAddress = _ btn.TopLeftCell.Address(external:=True) Exit For End If Next SH If Not btn Is Nothing Then MsgBox StrAddress End Sub '<<============ --- Regards, Norman Ok, very useful Norman. Then again: How to find the macro a button is assigned to? Ciao Bruno |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Row or Button Name
Hi Bruno,
Ok, very useful Norman. Then again: How to find the macro a button is assigned to? Try '============ Sub Tester3A() Dim Rng As Range Dim WB As Workbook Dim SH As Worksheet Dim BTN As Button Const sStr As String = "Button 12" '<<==== CHANGE Dim StrAddress As String Dim StrMacro As String Set WB = ActiveWorkbook '<<======= CHANGE For Each SH In WB.Worksheets On Error Resume Next Set BTN = SH.Buttons(sStr) On Error GoTo 0 If Not BTN Is Nothing Then StrAddress = _ BTN.TopLeftCell.Address(external:=True) StrMacro = BTN.OnAction Exit For End If Next SH If Not BTN Is Nothing Then MsgBox StrAddress _ & vbNewLine & StrMacro End Sub '<<============ --- Regards, Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create floating button based on button click in menu | Excel Programming | |||
Adding .xla button for Toggle Calculation Button | Excel Programming | |||
How do I lock a radio button group if a N/A button is selected | Excel Discussion (Misc queries) | |||
Finding a button | Excel Programming | |||
Delete a custom button by holding down the ALT key and dragging the button off the toolbar | Excel Programming |