Lock ComboBox/ MsgBox as Error

[joshua1990]

Good Morning!


I have a Userform1 with 8 different combo boxes. Now I want to make it a little easier for the user and let him make only one selection.
So either some MsgBox should appear if no or more than 1 combo boxes were chosen, or any other lock.


Does anyone have an idea for this?
Do you need an example table for this?




With best regards!

[p45cal]

Some simple code in the attached.

[SamT]

Since the "Blocking" would occur as soon as any one CB "Changed", "selecting" more than one is a non issue.

The issue of "None" selected can be handled with a Boolean Flag set "True" as soon as any one CB is "Changed" since it only becomes an issue when some next action is taken.

The problem of allowing the User to change their mind about a particular CB can be handled when the User changes their original selection to the Default option of that CB.

I don't like to ever use Control Type to set up a set of controls, since if I ever want to add a same Type control that should not be part of the Set, I would have to rewrite much of the code.

A simple method is to create a UserForm level String Constant containing the names of the Controls in the Set.
Const ControlSet As String = "CB1, CB2, CBFirstName, Etc"

A slightly more complex method does make a more robust structure. Create a UF level Variable and fill it with an Array in the UserForm_Initialize sub.

Dim ControlSet As Variant

Private Sub UserForm_Initialize()
ControlSet = Array("CB1", "CB2", "CBFirstName", "Etc")
End Sub

The String Method requires your code to access all Controls in the UF, then if the Control Name is in the string, operate on it, else skip to the next Control.

The Array method allows you to use the For... To... Next method to avoid checking all Control Names against a list. An Example follows:

Sub BlockAll(Optional Except As String, Optional UnBlockEm As Boolean)
Dim i as long

If Not UnBlockEm Then
If Except <> "" Then
For i = LBound(ControlSet) to UBound(ControlSet)
If Not ControlSet(i) = Except Then _
Me.Controls(ControlSet(i). Enabled = False
Next
Else
Msgbox "You must name a control not to block"
End If
Else
For i = LBound(ControlSet) to UBound(ControlSet)
Me.Controls(ControlSet(i). Enabled = True
Next
End If
End Sub

Now, Each CB must set the BooleanFlag = True/False, and must call BlockAll, pass its own name to the sub and maybe, set UnBlockEm.

Private Sub ComboBox1_Change(Blah, Blah)
If Me.ComboBox1.ListIndex = 0 Then
BooleanFlag = False
BlockAll UnBlockEm = True
Else
BooleanFlag = true
BlockAll Except = "ComboBox1"
End If
End sub

All code off the top of my head and is untested and not checked for errors. In fact, I never opened Excel while writing this post.