VBA Excel: Multiple combobox control

Hi,

Is there a way to control multiple items on a userform at the same time?

A simplified example is:
Change this:
Private Sub ComboBox1_Change()

Label4.Caption = ComboBox1.Value
Label5.Caption = Combobox2.value

End Sub

Open in new window


To this:
Private Sub ComboBox1_Change & Combobox2_Change()

Label4.Caption = ComboBox1.Value
Label5.Caption = Combobox2.value

End Sub

Open in new window


and likewise for other private sub commands such as _Click, _mousemove etc

Many Thanks
martinibboAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
Sure.
1. Add a new Class module to your workbook project (I'll assume the default name of Class1, though of course you should change it to something more descriptive)
2. Add this code:
Option Explicit

Private WithEvents m_combo As MSForms.ComboBox
Public Property Set Combo(cbo As MSForms.ComboBox)
   Set m_combo = cbo
End Property
Private Sub m_combo_Change()
   MsgBox "Changed value for " & m_combo.Name
End Sub

Private Sub m_combo_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
   Debug.Print m_combo.Parent.Name; X; Y
End Sub

Open in new window


Now, add the following code to your userform - I have assumed the comboboxes already exist. If you are adding them at runtime, you can cater for that too.
Option Explicit
Dim colCombos As Collection

Private Sub UserForm_Initialize()
   Dim ctl As MSForms.Control
   Dim oHandler As Class1
   Set colCombos = New Collection
      
   ' loop through controls and hook up comboboxes
   For Each ctl In Me.Controls
      If TypeName(ctl) = "ComboBox" Then
         Set oHandler = New Class1
         Set oHandler.Combo = ctl
         ' add class instance to collection so it doesn't get destroyed at the end of this routine
         colCombos.Add oHandler
      End If
   Next ctl
End Sub

Open in new window


Now run the form. As you mouse over a combo you should see information output to the Immediate Window, and as you change a combo you will get a message as to which combo you are changing.

HTH,
Rory
0
 
Rory ArchibaldConnect With a Mentor Commented:
There are two methods (depending on which events you wish to trap). The simplest way is to create a separate routine that is called by each control:
Sub UpdateCaptions()
Label4.Caption = ComboBox1.Value
Label5.Caption = Combobox2.value
End Sub
Private Sub ComboBox1_Change()
Call UpdateCaptions
End Sub
Private Sub ComboBox2_Change()
Call UpdateCaptions
End Sub

Open in new window


The second is more involved and requires you to create a class for each control type you wish to monitor, with a variable of that control type declared WithEvents.

I can provide an example of that if you wish. Note that not all events can be trapped using this approach - Exit, Enter and Before/AfterUpdate events cannot.
0
 
martinibboAuthor Commented:
Hi Rory,

Please can you provide a short example of option 2 so that I can have a play.

I am trying to cut down the volume of repeated code as I have a lot of comboboxes.
Another example would be combobox1- combobox50 all doing the following code:

Private Sub comboBox1_MouseMove( _
                        ByVal Button As Integer, ByVal Shift As Integer, _
                        ByVal X As Single, ByVal Y As Single)
''' this section allows scrolling on comboboxes
                HookListBoxScroll Me, Me.ComboBox1
End Sub

Private Sub comboBox2_MouseMove( _
                        ByVal Button As Integer, ByVal Shift As Integer, _
                        ByVal X As Single, ByVal Y As Single)
                HookListBoxScroll Me, Me.ComboBox2
End Sub

Open in new window


This is similar to your option 1, but makes the volume of code quite long for a simple instruction.

Thanks
0
 
martinibboAuthor Commented:
Great stuff - many thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.