Referring to current control on a worksheet
 

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

Referring to current control on a worksheet
 

activesheet.buttons(selection.name)


"RichardGarfield" wrote in
message ...
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

Referring to current control on a worksheet
 

Hi Bob

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

Any other suggestions?

Thanks

Richard

"Bob Umlas" wrote:

activesheet.buttons(selection.name)


"RichardGarfield" wrote in
message ...
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

Referring to current control on a worksheet
 

if its the activex command button

worksheets("sheet1").CommandButton1

then add a . choosing the method required


"RichardGarfield" wrote:

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

Referring to current control on a worksheet
 

For controls from the forms toolbar...

MsgBox "Control Clicked: " & Application.Caller

--
HTH...

Jim Thomlinson


"RichardGarfield" wrote:

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

Referring to current control on a worksheet
 

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.

--
Rick (MVP - Excel)


"RichardGarfield" wrote in
message ...
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

Referring to current control on a worksheet
 

Many thanks to all for the replies.

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

Richard

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"RichardGarfield" wrote in
message ...
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