Trapping a CommandBarButton Event 1

[sdk]

Hi all,

I want to trap the click event on a menu item, to run code to be determined at run time. I have created a class with a single member (CommandBarButton) and have (successfully?) instantiated it in the system - they do display as required.

However, the click event is not being trapped. Below is the full code for the class, and code scrap for instances.

Any help appreciated.

Cheers & thanks

Steve

The Class ButtonsClass

Option Explicit

Public WithEvents mButton As Office.CommandBarButton


Private Sub Class_Initialize()

End Sub

Private Sub mButton_Click(ByVal Ctrl As Office.CommandBarButton, _
    CancelDefault As Boolean)
    
    Debug.Print "Clicked " & mButton.Caption

        
End Sub

Private Function IsCPReportCurrent() As Boolean
    Dim targetName As String
    
    targetName = "CPReportData.xls"
    
    makeCPCurrent = (Application.ActiveWorkbook.Name = targetName)
    

End Function

Instantiating the object

    ...
    Set cbtn = New ButtonsClass
    Set cbtn.mButton = cbc.Controls.Add
                       (Type:=msoControlButton)
    With cbtn.mButton
        .Caption = shNames(i)
        .BeginGroup = False
    End With
    ...

 

[combo]

You need to make cbtn public:

Public cbtn As ButtonsClass
...
Sub xyz
...
Set cbtn = New ButtonsClass
...

combo

 

[sdk]

If I could give two stars I would

Cheers

S

 

[sdk]

Alas, I ran the wrong code first up to test, which made it appear to be working when in fact the solution didn't.

- I made the obj public in the module
This had no effect

- I made the click event sub public in the class for the button member
This also had no effect.

Cheers & thanks

Steve

 

[combo]

You can try (ButtonsClass) for testing:

Option Explicit
Public WithEvents mButton As Office.CommandBarButton

Private Sub Class_Initialize()
Msgbox "ButtonsClass initialised"
End Sub

Private Sub mButton_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)

Msgbox "Clicked " & mButton.Caption

End Sub

Private Sub Class_Terminate()
Msgbox "ButtonsClass terminated"
End Sub

This will allow you to trace creation and destruction of cbtn. As long as the object is created, it should trap "click" event.
If you do not need to refer to object properties/methods, they can remain private. Same as event procedures, they are handled within the class.

There may be a problem with other part of the code.

You can also assign a macro to a button in standard module. Having reference to custom button, you can assign macro name (string) to button's OnAction property.

combo