Application.Caller with Command Buttons?

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?

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

RBee wrote:
>
> 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

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" wrote:

> 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
>
> RBee wrote:
> >
> > 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
>

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.


RBee wrote:
>
> 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" wrote:
>
> > 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
> >
> > RBee wrote:
> > >
> > > 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
> >

--

Dave Peterson