Need to Include a Message Box 1

[mmr3b9]

Hi,

I have a command button(cmdDerive) and a list box(listbox2) in a Microsoft Excel form. The list box has 11 items in it. When i select an item in the list box and click on the command button a calculation takes place.

I need to add code so that if i do not have an item in the list box selected then it will display a message box telling the user they must select an item.


Private Sub cmdDerive_Click()
Dim index
For index11 = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(index11) = True Then
If ListBox2.List(index11) = "Dqwi1" Then
Call Dqwi1
ElseIf ListBox2.List(index11) = "Dqwi2" Then
Call Dqwi2
ElseIf ListBox2.List(index11) = "Dqwi3" Then
Call Dqwi3
ElseIf ListBox2.List(index11) = "Dqwi4" Then
Call Dqwi4
ElseIf ListBox2.List(index11) = "Dqwi5" Then
Call Dqwi5
ElseIf ListBox2.List(index11) = "Dqwi6" Then
Call Dqwi6
ElseIf ListBox2.List(index11) = "Dqwi7" Then
Call Dqwi7
ElseIf ListBox2.List(index11) = "Dqwi8" Then
Call Dqwi8
ElseIf ListBox2.List(index11) = "Dqwi9" Then
Call Dqwi9
ElseIf ListBox2.List(index11) = "Dqwi10" Then
Call Dqwi10
ElseIf ListBox2.List(index11) = "Dqwi12" Then
Call Dqwi12
End If
End If
Next index11
End Sub


I tried adding this code in a few places but it did not work correctly:

If ListBox2.Selected(index11) = False Then
MsgBox "You must select a derived variable."
End If


Thanks,
Mike

 

[DreamerZ]

If you adjust your check a little it should work:

If ListBox2.ListIndex=-1 then
  msgbox "you must select a derived variable."
Else
 For index11 = 0 To ListBox2.ListCount - 1
        If ListBox2.Selected(index11) = True Then
            If ListBox2.List(index11) = "Dqwi1" Then
                Call Dqwi1
...
End if

The empty check will happen first and if it's not empty the other code will run. You could play around with that first If. It might work with ListBox2 = "" then...

Hope that helps.


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[mmr3b9]

Thanks DreamerZ for the response.

You are correct. The code that u wrote will display the message box if the list box is empty. That is one part that i needed, thanks.

But I also need the message box to display if the listbox is populated with the 11 items and if no items are selected.

Can you help me with this situation. Where none of the 11 items are selected and the command button is clicked, and the msg box will display.

Thanks,
Mike

 

[PHV]

Try something like this:

NoItemSelected = True
For index11 = 0 To ListBox2.ListCount - 1
  If ListBox2.Selected(index11) = True Then
    NoItemSelected = False
    If ListBox2.List(index11) = "Dqwi1" Then
      Call Dqwi1
    ElseIf ListBox2.List(index11) = "Dqwi2" Then
      Call Dqwi2
...
    ElseIf ListBox2.List(index11) = "Dqwi12" Then
      Call Dqwi12
    End If
  End If
Next index11
If NoItemSelected Then
  MsgBox "You must select a derived variable."
End If

Hope This Help
PH.

 

[mmr3b9]

Thanks PH for the response.

The code works for when no item is selected but it also displays the message box when an item is selected. If an item is selected then the message box should not display.

If you could think of some code that will make the message box only display if no items are selected i would really appreciate it.

Thanks,
mike

 

[DreamerZ]

The ListIndex property "identifies the currently selected item in a ListBox or ComboBox" (from Excel Help). The first item in the list has a value of 0. "When no rows are selected, ListIndex returns -1."

So, while the code won't check to see if there are any items in the list, it DOES check to see if anything in the list is selected.

Obviously, if the list isn't populated then there won't be anything to select and the user will get the error that nothing is selected. You could use a simple ListCount to check that the list is populated if necessary (If ListBox1.ListCount <1 then populate list).

ListCount help actually says that if no item in the list is selected then ListCount=0 and ListIndex = -1.


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[PHV]

You don't forgot the red line ?
NoItemSelected = True
For index11 = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(index11) = True Then
NoItemSelected = False
If ListBox2.List(index11) = &quot;Dqwi1&quot; Then