Solved

VBA Excel: Multiple combobox control

Posted on 2013-12-18
4
1,763 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 500 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 500 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

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.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

617 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