Solved

VBA Excel: Multiple combobox control

Posted on 2013-12-18
4
1,611 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 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
macro for same location path 2 19
Search for a value in Column? 5 21
Excel VBA When using VLookup 6 26
Excel 2010 - Comparison of texts in a cell 14 29
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

914 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now