Call UserForm from a variable number of ActiveX command buttons

I want to be able to call one user form from multiple ActiveX command buttons. The problem is, the number of command buttons depends on user input on another worksheet, so it's variable.

I've renamed all of the command buttons so they are named "CommandButton" & i, where i is an integer between 1 and, say, 200. I want each of these buttons to direct to the same UserForm where additional information can be entered.

I can't think of a way around the event-handler procedure name.

Sub [Command Button Name]_Click

to call the User Form. I won't know the command button names, because I won't know how many there are (max i) until the user inputs.

Basically, I want to create a For loop through the max i and have the event-handlers call the user form

Any ideas?

Andy,

Thank you so much for the attempt, but unfortunately this is not what I'm looking for.

I've already created the variable number of buttons, which depends on the number of entries on another worksheet (the code is just embedded with various other formatting and project-specific code, etc. so I'm not including it here) as OLEObjects, and I've renamed each of them "CommandButton" & i, where i is the number 1 to the max number of buttons. I'm really happy with how this works, where the buttons are placed, and it fits with everything else I've got going on, so it seems I don't need the first part of your answer. I also have other command buttons and OLEObjects on the same worksheet, which complicates things.

I would REALLY like help creating an event handler that calls one userform (which I've already created) from each of the buttons that has been created. Basically, I need to call an event handler for a variable number of buttons. After the user inputs information on another worksheet, the variable number will be defined, but to me, right now, it's unknown.

Typically, I would just write a procedure:

Sub CommandButton_Click
...

or, more specifically in this case:

Sub CommandButton1_Click
...

Sub CommandButton2_Click
...

and so on

but since I don't know how many buttons there will be, I don't know the names of each of the buttons (there could be CommandButton20, or CommandButton200).

Am I missing something from your answer? Any other advice?

Thanks so much,
Karen

They're on a worksheet.

The buttons will only invoke a userform, but as I understand it, you can't create Forms Buttons via VBA code. If there's a way to create Forms Buttons via VBA, I'd be happy to - but as it stands now, I've created OLEObject command buttons (which are ActiveX, yeah?).

Thanks!

Thanks Andy and mikerickson.

I changed the buttons to forms buttons (The commented out portion is what I had previously to create the ActiveX buttons):

i = 1

    For row = 6 To 200
        If Not IsEmpty(WS1.Cells(row, 2)) Then
            WS2.Cells(row + 3, 2) = WS1.Cells(row, 2)
            
            'Create ... command button for ...
                
                'With WS2.OLEObjects.Add(ClassType:="Forms.Commandbutton.1", Top:=WS2.Cells(row + 3, 1).Top, _
                'Left:=WS2.Cells(row + 3, 1).Left, height:=WS2.Cells(row + 3, 1).height, width:=WS2.Cells(row + 3, 1).width)
                    
                '.Object.Caption = "Select"
                'End With
                
                With ActiveSheet.Buttons.Add(WS2.Cells(row + 3, 1).Top, WS2.Cells(row + 3, 1).Left, WS2.Cells(row + 3, 1).height, _
                    WS2.Cells(row + 3, 1).width)
                    .Name = "Button" & i
                    .OnAction = "SGUserForm"
                    .Caption = "Select"
                End With
                i = i + 1
        End If
    Next row

and then a Macro ShowSGUserForm:

Option Explicit
Sub SGUserForm()

    SGMeasuresForm.Show

End Sub

which should activate the SGMeasuresForm UserForm I have. However, I get "Run Time Error 438 - Object Doesn't Support this Property or Method". The only thing I can think of is that the issue is that I'm calling (indirectly) a userform from a form button, rather than an ActiveX button. Any truth to this? I feel like it shouldn't matter because I'm doing it indirectly and calling the macro from the button and the userform from the macro.

Also, I used the same parameters (left,top,height,width) for my Form button that I did for my OLEObjects buttons and the formatting is all screwed up now. My parameters worked well in the OLEObjects.Add.

Lastly, Andy - I'm still trying to integrate your original code into mine to see if I can get it to fit my needs. I'll let you know if I make progress and go that route instead of the forms buttons.

mikeerickson,

Sorry for the late reply - just getting back to looking at it. Yes, it's in a normal code module (module named "ShowSGUserForm" - procedure called SGUserForm, as shown above), not the sheet's code module. Any ideas?

What I've tried was adapted from John Walkenbach's example, p.529 in "Excel 2013 Power Programming with VBA" (also found here: http://www.j-walk.com/ss/excel/tips/tip44.htm and what you suggested, Andy.

The difference (as you know) is that my buttons are on a worksheet, rather than a UserForm.

Alright, I feel like I'm really close. I'm back to using the Class Module idea -- thanks, Andy.

My class module, named BtnClass:

Option Explicit

Public WithEvents ButtonGroup As MSForms.CommandButton

Private Sub ButtonGroup_Click()
        Show SGMeasuresForm vbModeless
End Sub

Dim Buttons() As New BtnClass
Dim ButtonCount As Integer
Dim cbutton As OLEObject

' Create the button objects
    ButtonCount = 0
    For Each cbutton In WS2.OLEObjects
        If TypeName(cbutton.Object) = "CommandButton" Then
      '      'Skip the clear button
        '    If cbutton.Object.Name <> "ClearButton" Then
                ButtonCount = ButtonCount + 1
                ReDim Preserve Buttons(1 To ButtonCount)
          '      Set Buttons(ButtonCount).ButtonGroup = cbutton
          '      Set Buttons(ButtonCount).ButtonGroup = WS2.OLEObjects(ButtonCount).Object
           ' End If
        End If
            
    Next cbutton

I'm having two problems. The most important one:

The buttons don't do anything because I get errors with both of these attempts:

          '      Set Buttons(ButtonCount).ButtonGroup = cbutton
          '      Set Buttons(ButtonCount).ButtonGroup = WS2.OLEObjects(ButtonCount).Object

The second problem is that I'd like to skip over my Clear Button (an OLEObject), but something's wrong with my If statement:

        '    If cbutton.Object.Name <> "ClearButton" Then

Any help!? Thanks so much!