Multiple Active X CommandButtons

[mikeymay]

I have a number of Active X buttons in a sheet and I am using the

Private Sub btn1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

to show a shape holding a description of what the button will do once clicked.

As I will have multiple buttons for different functions, is it possible to create one set of code to work dynamically with the button index e.g. btn1, btn2, btn3, etc.

The code needs to de exactly the same for each button which is to make a shape visible, change it's caption and position above the button being hovered over.


Thanks

 

[GWteB]

What you're probably after can be done by having a custom class for handling the events of the command buttons on your worksheet. In that way the buttons share the same code although each button has its own instance of that class. These instances must be initialized. This is done through a separate procedure which will fire when the workbook opens.
The code assumes the following:
- only the command buttons on Sheet1 need their own instance of the custom class;
- the shape to be manipulated is (of course) also on Sheet;
- this shape is named "Rounded Rectangle 1".

This goes in a class module, to be renamed as Class_CbtnGroup

Option Explicit

Private WithEvents CbtnGroup  As CommandButton

Private oWsHost As Worksheet
Private oShape  As Shape

Friend Sub Init(ByVal argCbtn As CommandButton, ByVal argSheet As Worksheet, ByVal argShape As Shape)
    Set CbtnGroup = argCbtn
    Set oWsHost = argSheet
    Set oShape = argShape
End Sub

Private Sub CbtnGroup_Click()
'    
' == your code ==
'  
End Sub

Private Sub CbtnGroup_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    With oShape
        .Left = CbtnGroup.Left
        .Top = CbtnGroup.Top
        .TextFrame2.TextRange.Text = CbtnGroup.Name
    End With
End Sub

This goes in the module of ThisWorkbook:

Option Explicit

Private coll As Collection

Private Sub Workbook_Open()
    Call InitCbtnEvents
End Sub

Private Sub InitCbtnEvents()

    Dim clsBtn  As Class_CbtnGroup
    Dim oWs     As Worksheet
    Dim oCtl    As Object
    
    Set coll = New Collection
    Set oWs = ThisWorkbook.Sheets("Sheet1")                                 ' <<< change sheet name to suit
    For Each oCtl In oWs.OLEObjects
        If oCtl.progID = "Forms.CommandButton.1" Then
            Set clsBtn = New Class_CbtnGroup
            Call clsBtn.Init(argCbtn:=oCtl.Object, _
                             argSheet:=oWs, _
                             argShape:=oWs.Shapes("Rounded Rectangle 1"))   ' <<< change shape name to suit
            coll.Add clsBtn
        End If
    Next oCtl
End Sub

 

[mikeymay]

What you're probably after can be done by having a custom class for handling the events of the command buttons on your worksheet. In that way the buttons share the same code although each button has its own instance of that class. These instances must be initialized. This is done through a separate procedure which will fire when the workbook opens.
The code assumes the following:
- only the command buttons on Sheet1 need their own instance of the custom class;
- the shape to be manipulated is (of course) also on Sheet;
- this shape is named "Rounded Rectangle 1".

This goes in a class module, to be renamed as Class_CbtnGroup

Option Explicit

Private WithEvents CbtnGroup  As CommandButton

Private oWsHost As Worksheet
Private oShape  As Shape

Friend Sub Init(ByVal argCbtn As CommandButton, ByVal argSheet As Worksheet, ByVal argShape As Shape)
    Set CbtnGroup = argCbtn
    Set oWsHost = argSheet
    Set oShape = argShape
End Sub

Private Sub CbtnGroup_Click()
'   
' == your code ==
' 
End Sub

Private Sub CbtnGroup_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    With oShape
        .Left = CbtnGroup.Left
        .Top = CbtnGroup.Top
        .TextFrame2.TextRange.Text = CbtnGroup.Name
    End With
End Sub

This goes in the module of ThisWorkbook:

Option Explicit

Private coll As Collection

Private Sub Workbook_Open()
    Call InitCbtnEvents
End Sub

Private Sub InitCbtnEvents()

    Dim clsBtn  As Class_CbtnGroup
    Dim oWs     As Worksheet
    Dim oCtl    As Object
   
    Set coll = New Collection
    Set oWs = ThisWorkbook.Sheets("Sheet1")                                 ' <<< change sheet name to suit
    For Each oCtl In oWs.OLEObjects
        If oCtl.progID = "Forms.CommandButton.1" Then
            Set clsBtn = New Class_CbtnGroup
            Call clsBtn.Init(argCbtn:=oCtl.Object, _
                             argSheet:=oWs, _
                             argShape:=oWs.Shapes("Rounded Rectangle 1"))   ' <<< change shape name to suit
            coll.Add clsBtn
        End If
    Next oCtl
End Sub

Thanks!

I'll take a look at this over the weekend.