userform with a dynamic number of buttons

[robertvdb]

I have a userform with x buttons, where x is a dynamically changing figure, based on the non-empty cells in col A and B.

Now when I click e.g. employee3, I want - just as an example to keep things simple - to show a Msgbox ("this is employee 3").

I could do this manually for each button of course, but since the number of buttons changes, can I VBA-code this automatically ?

Thanks.

 

[Micron]

I have never done that but found this, which you may be able to adapt to your situation.

 

[dmt32]

Hi,

Give following a try & see if does what you want

Place Following code in your Userforms CODE PAGE

Private Sub UserForm_Initialize()

  AddButtons Me

End Sub

Insert a standard Module

Place Following code in the module

Dim ArrButtons() As New CommandButtonClass

Sub AddButtons(ByVal Form As Object)
    Dim arr           As Variant
    Dim ButtonCount   As Long
    Dim r             As Long, c As Long
    Dim ButtonAdd     As MSForms.CommandButton
   
    '-----------------------------------------------------------------------------------------------------------------
    '                                                   SETTINGS
    '                                          (Adjust Values As Required)
    '-----------------------------------------------------------------------------------------------------------------
   
    Const ButtonHeight          As Long = 20, ButtonWidth    As Long = 72
    Const ButtonTopStart        As Long = 6, ButtonLeft      As Long = 30
    Const ButtonSpace           As Long = 4
   
    '-----------------------------------------------------------------------------------------------------------------
   
    With ThisWorkbook.Worksheets("Sheet1")
        arr = .Range("A2").Resize(.Cells(.Rows.Count, 1).End(xlUp).Row - 1, 2).Value
    End With
   
    ButtonTop = ButtonTopStart
   
    For r = 1 To UBound(arr, xlRows)
       
        For c = 1 To UBound(arr, xlColumns)
           
            If Len(arr(r, c)) > 0 Then
           
                ButtonCount = ButtonCount + 1
               
                Set ButtonAdd = Form.Controls.Add("Forms.CommandButton.1", arr(r, c))
               
                ReDim Preserve ArrButtons(1 To ButtonCount)
                Set ArrButtons(ButtonCount).CommandButtonGroup = ButtonAdd
               
                With ButtonAdd
                    .Caption = arr(r, c)
                    .Width = ButtonWidth
                    .Height = ButtonHeight
                    .Left = IIf(c = 1, ButtonLeft, ButtonLeft + ButtonWidth + (ButtonSpace * 6))
                    .Top = ButtonTop
                End With
               
            End If
           
            Set ButtonAdd = Nothing
   
        Next c
       
        ButtonTop = ButtonTop + ButtonHeight + ButtonSpace
    
    Next r
   
End Sub

Note variable at top of procedure. This MUST site at very TOP of the code page OUTSIDE any procedure.

Insert a CLASS Module & Name it

CommandButtonClass << you must do this

Place following code in the class module

Public WithEvents CommandButtonGroup As MSForms.CommandButton

Private Sub CommandButtonGroup_Click()

'commandbutton name
MsgBox "This Is " & CommandButtonGroup.Name

'commandbutton caption
'MsgBox CommandButtonGroup.Caption

'userform name
'MsgBox CommandButtonGroup.Parent.Name

End Sub

Note variable at top of procedure. This MUST site at very TOP of the code page OUTSIDE any procedure.

when you open your userform you should see commandbuttons displayed in the similar layout as in your worksheet with their captions taken from range values.

Pressing any button you should get a result like attached image which in this example displays the commandbutton name.

Dave

 

[robertvdb]

Thanks Dmt ! Also thanks to Micron.

 

[dmt32]

Thanks Dmt ! Also thanks to Micron.

most welcome glad we were able to help & appreciate your feedback

Dave