ToggleButton's event Application.Caller

[drom]

Hi and thanks in advance!

I have like 25/30 ToggleButton_Change events on 1 WorkSheet.


eg:

ToggleButton1_Change
ToggleButton2_Change
ToggleButton3_Change
ToggleButton4_Change and so on.


is there any way to know to which ToogleButton it's been attached the

Private Sub ToggleButton9_Change() events????


kind of:

Private Sub ToggleButton9_Change()
   Debug.Print Application.Caller ???
   Debug.Print ToggleButton9.Name 'Work's Fine but I have to use some thing more generic
     'to use it in 25/30 ToggleButtons
End Sub

 

[Tom Urtis]

Your post is a bit confusing.

First, I think you used a misnomer with the word "events" when you wrote

"I have like 25/30 ToggleButton_Change events..."

I think you meant to say "controls".

But then you ask about Application.Caller which is a Forms control property and your code looks like you are using activex ToggleButton controls with a Change event. Speaking of, there are only 14 events with embedded activex ToggleButton controls, not 25/30 so what you probably need is a class module to ID the names instead of Application.Caller which will do you no good for activex.

Here is a link showing how to identify an activex control's name using a class module, which you can adapt for ToggleButtons:

http://www.mrexcel.com/forum/showthread.php?t=347430

 

[drom]

I have over 20 Private Sub CheckBox1_Click() macros on a Sheet named "Sheet01"
the name's of this over 20 buttons which appear on the sheet's name Box, when you are on design mode and right click on the buttons are:

ToggleButton1
ToggleButton2
ToggleButton3
ToggleButton4 and so on

the ToggleButton are pasted using the ActiveX controls

When I click any of this buttons on design mode, I get the wollowing macros:

Private Sub ToggleButton100_Click()

End Sub

Private Sub ToggleButton8_Click()

End Sub

so How can I know If I click the ToggleButton100 on the Sheet named "Sheet01"

the name Of this ToggleButton on a message box. Kind of:

Private Sub ToggleButton100_Click()
 msgbox "ToggleButton100"
'But using code like eg:
MsgBox ClickedToggleButton.name   ????????????????
Debug.Print ClickedToggleButton.name ????????????????
End Sub

Thanks!

 

[drom]

SOLVED: ToggleButton's event Application.Caller

    For Each oleCtl In Worksheets("Sheet1").OLEObjects
      If TypeOf oleCtl.Object Is msforms.ToggleButton Then
    .......................
......................

gets the Collection of names but just one as requested??

 

[Tom Urtis]

Here is the code from the link I posted, slightly modified to fit your situation for toggle buttons.

In a new fresh standard module paste in this entire code, making sure that if you already have an existing AutoOpen macro, you delete it and replace it with this:

Public myControls As Collection
 
 
Sub Auto_Open()
With Application
.ScreenUpdating = False
Dim oleCtl As OLEObject, ctl As Class1
Set myControls = New Collection
For Each oleCtl In Worksheets("Sheet1").OLEObjects
If TypeOf oleCtl.Object Is msforms.ToggleButton Then
Set ctl = New Class1
Set ctl.myTglBtn = oleCtl.Object
myControls.Add ctl
End If
Next
.ActiveWindow.WindowState = xlMaximized
.Goto Worksheets("Sheet1").Range("A1"), True
End With
End Sub

From the VBE's menu bar, click Insert Class Module. Accept the class module's default name of Class1.

In your new Class1 module, paste this in:

Public WithEvents myTglBtn As msforms.ToggleButton
 
 
Private Sub myTglBtn_Click()
MsgBox "Hello, my name is ''" & myTglBtn.Name & "''." & vbCrLf & _
"My caption is ''" & myTglBtn.Caption & "''.", 64, _
"You just clicked me:"
End Sub

Save the workbook.

Close the workbook.

Open the workbook.

Test by clicking a toggle button control on Sheet1. It works for me, no problem.