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

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

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

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

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

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

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

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

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