PDA

View Full Version : [SOLVED] Attach event to multiple ComboBoxes



pulsar777
07-20-2019, 04:36 AM
Hi, I have a multipage with total of 50 comboboxes.
When Userform loads, they start with same items sourced from Range("A1:A50")
As I start to select values box by box, I'd like to have number of options in next ComboBox shrink by 1.
So I'd like to attach one change event to all 50 combos instead of 50 subroutines.

I found similar thread:
http://www.vbaexpress.com/forum/showthread.php?62395-Class-Event-for-ComboBox-Enter-(Excel-2013)&highlight=event+during+runtime

However, it's beyond my comprehension.
Can anybody help please?

p45cal
07-20-2019, 07:40 AM
The attached should get you started. Theres' code in Module1:
Sub Button1_Click()
UserForm1.Show
End Sub
which is called when the user clicks the button on Sheet1.
Code in the Userfrom1's code-module:
Dim Combos() As New CmboClass

Private Sub UserForm_Initialize()
Dim ComboCount As Long, ctl As Control
ComboCount = 0
For Each ctl In Me.Controls
If TypeName(ctl) = "ComboBox" Then
ComboCount = ComboCount + 1
ReDim Preserve Combos(1 To ComboCount)
Set Combos(ComboCount).ComboGroup = ctl
End If
Next ctl
End Sub
Which put the comboxes you want to respond into a group (Combos). In this case it's all of them but you can be selective if you want.
Code in the CmboClass class module:
Public WithEvents ComboGroup As MsForms.ComboBox

Private Sub ComboGroup_Change()
Msg = "You changed " & ComboGroup.Name & vbCrLf & vbCrLf
Msg = Msg & "Its value: " & ComboGroup.Value & vbCrLf
Msg = Msg & "Left Position: " & ComboGroup.Left & vbCrLf
Msg = Msg & "Top Position: " & ComboGroup.Top
MsgBox Msg, vblnformation, ComboGroup.Name
End Sub
which just demonstrates it's working correctly.
What I haven't done is anything about changing the other comboboxes' lists of options; I can do it in a convoluted way, but it would be easier if I were able to expose the Combos object to the class module.
To delve further I'd like to see a workbook more closely representing your query.

(ps the file name I've used is NOT because it's the max number of rows in older versions of Excel, it's the number in the url of this thread! I think this tells you I might be a bit geeky.)

pulsar777
07-21-2019, 03:07 AM
p45cal, you are geeky indeed. It's much simpler compared to the other thread. My lucky number 65536 got me this prize and hopefully some tax returns in future. Thanks a ton !

p45cal
07-21-2019, 03:58 AM
As I start to select values box by box, I'd like to have number of options in next ComboBox shrink by 1.

I can do it in a convoluted way
See attached; 12 comboboxes, each one's options reduce/increase as others take on options.