Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA Excel: Multiple combobox control

Posted on 2013-12-18
4
Medium Priority
?
1,904 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:martinibbo
  • 2
  • 2
4 Comments
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 2000 total points
ID: 39726313
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
 

Author Comment

by:martinibbo
ID: 39726333
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 39726369
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
 

Author Comment

by:martinibbo
ID: 39726439
Great stuff - many thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question