VBA Userform with multiple combobox but same options

becklog

New Member
Joined
Dec 26, 2016
Messages
36
Hi,

I am working on something that requires multiple combobox. Some have unique options but most only require a yes/no option. Is there a way to add that option from combobox10 to combobox 30?

Thank you!
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
L

Legacy 456155

Guest
Have you considered using option buttons within frames or by setting the GroupName property?

1601593019273.png
 
L

Legacy 456155

Guest
Here is an option using a callback.

Create a class module named CommonComboChange. Add the following:
VBA Code:
Option Explicit

Private WithEvents cb As MSForms.ComboBox
Private CallBackParent As Object

Friend Sub Init(caller As Object, o As MSForms.ComboBox)
    Set cb = o
    Set CallBackParent = caller
End Sub

Private Sub cb_Change()
    CallBackParent.MultiComboChange cb
End Sub

Something along these lines in your userform:
VBA Code:
Option Explicit

Private c As New Collection

Private Sub UserForm_Initialize()
    Dim ccc As CommonComboChange, cb As MSForms.ComboBox
    
    For Each cb In Controls
        If Val(Right(cb.Name, 2)) > 9 And Val(Right(cb.Name, 2)) < 31 Then
            Set ccc = New CommonComboChange
            ccc.Init Me, cb
            c.Add ccc
        End If
    Next
End Sub

Public Sub MultiComboChange(cb As MSForms.ComboBox)
    MsgBox "You clicked " & cb.Text & " from " & cb.Name
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,545
Messages
5,529,458
Members
409,879
Latest member
Aussie_Excel_Wanna_Be
Top