VBA pop message box based on certain criteria

[jimmisavage]

I have a userform where I want to make sure the user is inputting the necessary data.

ComboBox41 only needs to be completed IF ComboBox1 has data and ComboBox21 is empty.

So if ComboBox1 is empty we can move on.
Or if ComboBox1 has data and ComboBox21 has data we can move on.

 

[Roderick_E]

if trim(combobox41.text)="" or ( trim(combobox41.text)<>"" and trim(combobox21.text)<>"") then
'do stuff
endif

 

[jimmisavage]

Hi Roderick,
This works to a degree but it still wants me to add a value to ComboBox41 when ComboBox1 is empty. It should let me continue if ComboBox1 is empty.

 

[Roderick_E]

You must have another if-conditional somewhere in your existing code???

 

[jimmisavage]

Actually my example is a simplified version. This is repeated 20 times on the same form but with the next box in the sequence.
So i'm doing something like this, 20 times...

If Trim(ComboBox41.Text) = "" Or (Trim(ComboBox41.Text) <> "" And Trim(ComboBox21.Text) <> "") Then
MsgBox "Enter a default value"
Exit Sub
End If


If Trim(ComboBox42.Text) = "" Or (Trim(ComboBox42.Text) <> "" And Trim(ComboBox22.Text) <> "") Then
MsgBox "Enter a default value"
Exit Sub
End If


If Trim(ComboBox43.Text) = "" Or (Trim(ComboBox43.Text) <> "" And Trim(ComboBox23.Text) <> "") Then
MsgBox "Enter a default value"
Exit Sub
End If


If Trim(ComboBox44.Text) = "" Or (Trim(ComboBox44.Text) <> "" And Trim(ComboBox24.Text) <> "") Then
MsgBox "Enter a default value"
Exit Sub
End If

 

[Roderick_E]

Why not accumulate the potential missing data and only advise the user once?

If Trim(ComboBox41.Text) = "" Or (Trim(ComboBox41.Text) <> "" And Trim(ComboBox21.Text) <> "") Then
errsmsg = errmsg & vbcr & "Enter value in...<name of place to enter>"
End If
If Trim(ComboBox42.Text) = "" Or (Trim(ComboBox42.Text) <> "" And Trim(ComboBox22.Text) <> "") Then
errsmsg = errmsg & vbcr & "Enter value in...<name of place to enter>"
End If
If Trim(ComboBox43.Text) = "" Or (Trim(ComboBox43.Text) <> "" And Trim(ComboBox23.Text) <> "") Then
errsmsg = errmsg & vbcr & "Enter value in...<name of place to enter>"
End If
If Trim(ComboBox44.Text) = "" Or (Trim(ComboBox44.Text) <> "" And Trim(ComboBox24.Text) <> "") Then
errsmsg = errmsg & vbcr & "Enter value in...<name of place to enter>"
End If

'one time errmsg check
if trim(errmsg)<>"" then
msgbox "Input missing data:" & vbcr errmsg, vbcritical,"ALERT"
exit sub
endif

 

[jimmisavage]

I don't think that would really work for my purpose.
I really only need the rule to apply if ComboBox1 (followed by 2, 3 and 4 in the above example) has data in it. 1 & 2 might have data, which would then need the rule but 3 and 4 could be empty so the rule wouldn't be needed.

 

[jimmisavage]

Does anyone have any other ideas on how this might work? I believe we're close but it's missing one step in the chain

 

[jimmisavage]

I have also X-Posted this question: https://www.excelforum.com/showthread.php?t=1216805&p=4827928#post4827928

 

[Roderick_E]

So to simplify, you want to make sure all boxes have something in them? If so, then perhaps

for x = 1 to 50 'assumes 50 comboboxes
if trim(userfor1.controls("ComboBox" & x))="" then
errmsg = errmsg & vbcr "populate " & x & " box"
endif
next x
if errmsg <>"" then
msgbox "Input missing data in following boxes:" & vbcr & errmsg, vbcritical,"ALERT"
exit sub
endif