togglebutton procedure - shortcut reference to togglebutton name? 1

[electricpete]

On tek-tips.com sister site eng-tips.com, someone presented a problem where they end up wanting to use a whole bunch of toggle buttons with a somewhat customized appearance, but they don't want to have to customize the code for each button too mcuh (ideally the same code in each button)

http://www.eng-tips.com/viewthread.cfm?qid=223890&page=1

The code that I presented was

Private Sub ToggleButton2_Click()
    With ToggleButton2
        If .Object.Value Then
            .BackColor = &H80000002
        Else
            .BackColor = &H80000003
        End If
    End With
End Sub

The customization that would be required is to change the name of the togglebutton listed after with in each procedure.

It got me to wondering if there is generic way that an event procedure can refer to it's "parent object" (not sure if that's the right term) - in this case the togglebutton. For example, in object modules we can use "me" to refer to the parent object. It doesn't get me to the button in this case because the parent object for this module would be a spreadsheet.

Is there some generic variable or object (similar to "me") that I can substitue into the above code in place of ToggleButton2 ?

 

[SkipVought]

How about the Caller Property?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[electricpete]

Thanks skip. I put Debug.Print Application.Caller at the the beginning of the togglebutton procedure. It gives the following error:
Error 2023

 

[electricpete]

I should clarify, that's the error I get when I execute the button the normal way (by pressing the button)

 

[TonyJollans]

The way to do this is to use CommandButton Events.

Create a Class Module, call it, say, EventTrap, and code it like this:

[blue][green]' Call Module "ClickTrap"[/green]

Option Explicit

Public WithEvents Button As CommandButton

Private Sub Button_Click()
    MsgBox Button.Caption & " was clicked"
End Sub[/blue]

Put your own code in the button click procedure, of course. [blue]Button[/blue] is a reference to the clicked CommandButton.

In order to initialise things, put this code in your ThisWorkbook module:

[blue]Option Explicit

Private ClickTraps() As ClickTrap

Private Sub Workbook_Open()

    Dim OneSheet As Worksheet
    Dim OneShape As Shape
    
    For Each OneSheet In Me.Worksheets
    For Each OneShape In OneSheet.Shapes
    
        If OneShape.Type = msoOLEControlObject Then
            If OneShape.OLEFormat.progID = "Forms.CommandButton.1" Then
                If (Not ClickTraps) = True Then
                    ReDim ClickTraps(1 To 1)
                Else
                    ReDim Preserve ClickTraps(LBound(ClickTraps) To UBound(ClickTraps) + 1)
                End If
                Set ClickTraps(UBound(ClickTraps)) = New ClickTrap
                Set ClickTraps(UBound(ClickTraps)).Button = OneShape.OLEFormat.Object.Object
            End If
        End If
    
    Next OneShape, OneSheet
    
End Sub[/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website