Application.Caller with Command Buttons?

[Guest]

I'm trying ot use following line of code...

ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row

....to generically determine the row in which a button was clicked (which
initiates a macro). The code works great for Buttons created with the Forms
toolbar, but does not work for Command Buttons created with the Controls
Toolbox toolbar (which, I assume is ActiveX). I need to use the latter type
so I can also programatically change its BackColor.

The error seems to be Error 2023.

Any way around this?

 

[Dave Peterson]

I'm not sure how the topleftcell and backcolor relate, but since you're in that
button's click procedure, you know what button started the macro:

Option Explicit
Private Sub CommandButton1_Click()
MsgBox Me.CommandButton1.TopLeftCell.Address
End Sub

or...

Option Explicit
Private Sub CommandButton1_Click()
MsgBox Me.CommandButton1.TopLeftCell.Address
Me.CommandButton1.BackColor = &HFF&
End Sub

 

[Guest]

Yes, thank you. While I'm sure that code would work, my issue is that I have
several command buttons on several sheets, so I'm trying to write it
generically...so that I don't have unique code for each button, and can
simply refer all to same procedure.

 

[Dave Peterson]

Since you're using commandbuttons from the control toolbox toolbar, aren't you
going through each button's _click event to call the common procedure?

Or have you set up some class module?

If you're going through the commandbutton's _click event, then just pass the
button to the common procedure.

Behind the worksheet:
Option Explicit
Private Sub CommandButton1_Click()
'do some setup?
Call CommonProc(Me.CommandButton1)
'do some clean up???
End Sub


In a general module:
Option Explicit
Sub CommonProc(myCMDBTN As MSForms.CommandButton)
If Rnd > 0.5 Then
myCMDBTN.BackColor = &HFF&
Else
myCMDBTN.BackColor = &HFF00&
End If
End Sub

===
But if you're using commandbuttons from the control toolbox toolbar, you won't
be able to rightclick on them and assign a common macro.