CODEWITHSUNDEEP
excelvbauserform
Kyriacos Evgeniou
Kyriacos Evgeniou

Reputation: 11

Problems with comboboxes in a userform

I am working within a userform and I am coding on comboboxes. I have put a drop down list in my combobox using .additem, and every time a user presses on one of the items in the list, a message box should appear.

For some reason, my first code makes the message box reappear on the next row within the combobox so when the second row is pressed I have two message boxes instead of one.

Is it because of the and function? Is there another way to do this?

To clarify, ComboBox1.ListIndex = 2 (2 message boxes appear whereas I need only the one which is the one I have coded) and ComboBox1.ListIndex = 3 (3 message boxes appear instead of 1).

If ComboBox1.ListIndex = 1 And Msgbox("Do you want to create a new company?", vbYesNo) = vbYes Then UserForm1.Show

If ComboBox1.ListIndex = 2 And Msgbox("Do you want to open the reports screen?", vbYesNo) = vbYes Then UserForm2.Show

If ComboBox1.ListIndex = 3 And Msgbox("Are you sure", vbYesNo) = vbYes Then Unload AccountsVbaPro

Upvotes: 1

Views: 144

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71177

The And operator (not function) doesn't short-circuit1, so in order to evaluate whether the Boolean expression result is True, VBA needs the result of the MsgBox function... for each condition.

'both foo and bar need to be evaluated to know whether DoSomething needs to run:
If foo And Bar Then DoSomething

Make the MsgBox calls conditional - I'd suggest using a Select Case block, to avoid repeating the ComboBox1.ListIndex member access every time:

Select Case ComboBox1.ListIndex
   Case 1
       If Msgbox("Do you want to create a new company?", vbYesNo) = vbYes Then UserForm1.Show
   Case 2
       If Msgbox("Do you want to open the reports screen?", vbYesNo) = vbYes Then UserForm2.Show
   Case 3
       If Msgbox("Are you sure", vbYesNo) = vbYes Then Unload AccountsVbaPro
End Select

Note that UserForm1.Show / UserForm2.Show are likely going to cause problems eventually, as is Unload AccountsVbaPro if that code is in the code-behind of a form named AccountsVbaPro.

1A short-circuiting operator does not exist in VBA. In e.g. VB.NET, you get to use the AndAlso and OrElse operators, which do. The consequence of short-circuiting logical operators, is that evaluating a Boolean expression can bail out once its result is known:

If True Or True Or False Then ' all operands need to be evaluated

vs

If True OrElse True OrElse False Then ' evaluation stops at the first True; 2nd & 3rd operands are skipped

Upvotes: 1

Related Questions