Run macro only after all combo boxes are filled

[Klemen25]

Hy

I have a form with a few comboboxes the users have to use to input the
relevant data. At the end there is a button that runs a macro that
calculates result according the selections made in combo boxes.

I would like to know if there is a way to restrict this macro to be
run only if all the comboboxes were used (selection from the list was
made in the each combobox).
Any simple way to do this?

Thank you

 

[Dave Peterson]

One nice way that could be used is to keep the "ok" button disabled until all
the controls are non-empty.

I created a small userform with 2 comboboxes (I'm lazy!), a label (for warning
messages), and two commandbuttons (cancel and ok).

This was the code behind the userform:

Option Explicit
Private Sub ComboBox1_Change()
Call CheckOkBTN
End Sub
Private Sub ComboBox2_Change()
Call CheckOkBTN
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
MsgBox "ok was clicked"
End Sub
Private Sub UserForm_Initialize()

With Me.ComboBox1
.Style = fmStyleDropDownList
.AddItem "A"
.AddItem "B"
End With

With Me.ComboBox2
.Style = fmStyleDropDownList
.AddItem "C"
.AddItem "D"
End With

With Me.CommandButton1
.Caption = "Cancel"
.Enabled = True
.Cancel = True
End With

With Me.CommandButton2
.Enabled = False
.Caption = "Ok"
End With

Me.Label1.Caption = "Please fill in all comboboxes"

End Sub
Private Sub CheckOkBTN()

Dim ctrl As Control
Dim OkToContinue As Boolean

OkToContinue = True

For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.ComboBox Then
If ctrl.ListIndex < 0 Then
'not completed
OkToContinue = False
Exit For 'stop looking
End If
End If
Next ctrl

Me.CommandButton2.Enabled = OkToContinue

If OkToContinue = True Then
Me.Label1.Caption = ""
Else
Me.Label1.Caption = "Please fill in all comboboxes"
End If

End Sub

If you have other controls on the userform, you could check to make sure they're
ok, too.