Referring to current control on a worksheet

  • Thread starter Thread starter RichardGarfield
  • Start date Start date
R

RichardGarfield

I need to be able to refer to the current commandbutton on a worksheet
without specifying the name each time - I have about 25 on each worksheet.

Is there a way of doing this? I have tried 'ActiveControl' but it seems not
to work on a worksheet.

Please can anyone help?

Richard
 
Hi Bob

I tried your code and it generates an error 1004 - Application-defined or
object-defined error.

Any other suggestions?

Thanks

Richard
 
if its the activex command button

worksheets("sheet1").CommandButton1

then add a . choosing the method required
 
For controls from the forms toolbar...

MsgBox "Control Clicked: " & Application.Caller
 
Since you said "commandbutton", I'm assuming we are talking about ActiveX
controls, right? Then you can do the following. Create a "global" object
variable (declare it in the General/Declarations section of the worksheet's
code window), set it to the CommandButton (actually, this would work for any
control) in each CommandButton's GotFocus event and clear it again in the
CommandButton's LostFocus event. That way, while the CommandButton is
selected, the global variable will be referencing it. For example...

Dim ActiveControl As Object

Private Sub CommandButton1_GotFocus()
Set ActiveControl = CommandButton1
End Sub

Private Sub CommandButton1_LostFocus()
Set ActiveControl = Nothing
End Sub

Private Sub CommandButton2_GotFocus()
Set ActiveControl = CommandButton2
End Sub

Private Sub CommandButton2_LostFocus()
Set ActiveControl = Nothing
End Sub

You would just need to extend the setup above for all of your
CommandButtons.
 
Many thanks to all for the replies.

Rick - I have adapted your suggestion using the onclick event and it is
working fine.

Richard
 
Back
Top