Multiple Active X CommandButtons

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,483
I have a number of Active X buttons in a sheet and I am using the
VBA Code:
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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
VBA Code:
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:
VBA Code:
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

Well-known Member
Joined
Jan 17, 2006
Messages
1,483
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
VBA Code:
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:
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,765
Messages
5,626,742
Members
416,201
Latest member
brianhf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top