Command button Array


Well-known Member
Jul 1, 2009
Office Version
  1. 2019
  2. 2016
  1. Windows
I'm wanting what I think would be a Command Button Control Array.
Where a number of buttons can share a common click event and pass something to identify which button was clicked.

I found this via Google

    Private arCommandButton(1 To 3) As New Class1
    Private Sub init()
        Set arCommandButton(1).ButtonEvents = cmd1
        arCommandButton(1).Index = 1
        Set arCommandButton(2).ButtonEvents = cmd2
        arCommandButton(2).Index = 2
        Set arCommandButton(3).ButtonEvents = cmd3
        arCommandButton(3).Index = 3
    End Sub
    'In a Class Module
    Public WithEvents ButtonEvents As MSForms.CommandButton
    Public Index As Integer
    Private Sub ButtonEvents_Click()
        MsgBox "Button clicked.  The Index is: " & Index
    End Sub

However I can't get this to work. Perhaps something is missing?
Any help with this or alternative method appreciated.

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Could you not use a simple method like this:

In the sheet level
Private Sub CommandButton1_Click()
  Call ButtonPress(1)
End Sub

Private Sub CommandButton2_Click()
  Call ButtonPress(2)
End Sub

In a standard module
Sub ButtonPress(BtnNum)
  Select Case BtnNum
    Case 1
      'Some code
    Case 2
      'some more code
    Case 3
      'Even more
  End Select
End Sub
Upvote 0
Thanks. That would work, yes, but I'd still need 48 individual (there's 48 buttons used as colour picker) click events.
I'm sure I had a solution (way above my code ability) but I can't find it now. It did it as one click event and the setup was very small too.
Upvote 0
If your looking for a color picker, I found this from here:

The TestColorPicker is just my input to see how it worked. This uses a dialog for the user to select any color and returns the results. I like it!

What do you think?


Sub testcolorpicker()
  Dim Red As Integer
  Dim Green As Integer
  Dim Blue As Integer
  Dim ChosenColor As Long
  ChosenColor = PickNewColor
  Color2RGB ChosenColor, Red, Green, Blue
  MsgBox "Red: " & Red & vbLf & "Green: " & Green & vbLf & "Blue: " & Blue
End Sub

'Picks new color
Function PickNewColor(Optional i_OldColor As Double = xlNone) As Double
  Const BGColor As Long = 13160660  'background color of dialogue
  Const ColorIndexLast As Long = 32 'index of last custom color in palette
  Dim myOrgColor As Double          'original color of color index 32
  Dim myNewColor As Double          'color that was picked in the dialogue
  Dim myRGB_R As Integer            'RGB values of the color that will be
  Dim myRGB_G As Integer            'displayed in the dialogue as
  Dim myRGB_B As Integer            '"Current" color
    'save original palette color, because we don't really want to change it
    myOrgColor = ActiveWorkbook.Colors(ColorIndexLast)
    If i_OldColor = xlNone Then
      'get RGB values of background color, so the "Current" color looks empty
      Color2RGB BGColor, myRGB_R, myRGB_G, myRGB_B
      'get RGB values of i_OldColor
      Color2RGB i_OldColor, myRGB_R, myRGB_G, myRGB_B
    End If
    'call the color picker dialogue
    If Application.Dialogs(xlDialogEditColor).Show(ColorIndexLast, _
       myRGB_R, myRGB_G, myRGB_B) = True Then
      '"OK" was pressed, so Excel automatically changed the palette
      'read the new color from the palette
      PickNewColor = ActiveWorkbook.Colors(ColorIndexLast)
      'reset palette color to its original value
      ActiveWorkbook.Colors(ColorIndexLast) = myOrgColor
      '"Cancel" was pressed, palette wasn't changed
      'return old color (or xlNone if no color was passed to the function)
      PickNewColor = i_OldColor
    End If
End Function

'Converts a color to RGB values
Sub Color2RGB(ByVal i_Color As Long, _
  o_R As Integer, o_G As Integer, o_B As Integer)
  o_R = i_Color Mod 256
  i_Color = i_Color \ 256
  o_G = i_Color Mod 256
  i_Color = i_Color \ 256
  o_B = i_Color Mod 256
End Sub
Upvote 0

Forum statistics

Latest member

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
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 "".
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