Solved

VBA Excel: Multiple combobox control

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

746 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

9 Experts available now in Live!

Get 1:1 Help Now