Wrong Msgbox displaying

[mardy_bum]

Hi,

I have struggled to find where I have gone wrong, I have a userform which has a search button that populates a listbox, the user then selects from the listbox and clicks on a button to add the record. That bit works fine, however I added in two checks when they click the add button, first to make sure the list has records in it, and the second to make sure they have selected something from the list. if the check fails an error message is displayed - so far it is displaying the same messagebox for both. Please help me see the wood from the trees..
my code is as follows:


Private Sub cmdAdd_Click()
Dim NextRow As String
Dim NextRow2 As Integer
Dim NumSelected As Integer
Dim y As Integer
Dim z As Integer
NumSelected = 0
NextRow = ActiveCell.Row
If lstResults2.ListIndex <> -1 Then
GoTo PopulatedList
'This is the MsgBox that displays
Else: MsgBox "There are no stakeholders that match your search. Please try a different search, or create a new stakeholder.", Title:="Unsuccessfull search"
End If
Exit Sub
PopulatedList:
With lstResults2
For y = 0 To .ListCount - 1
If .Selected(y) Then NumSelected = NumSelected + 1
Next y
End With
If NumSelected <> 0 Then
GoTo IndividualSelected
'This is the MsgBox that isn't showing
Else: MsgBox "You need to select a stakeholder to add!", Title:="No stakeholder selected"
End If
Exit Sub
IndividualSelected:
For z = 0 To lstResults2.ListCount - 1
If lstResults2.Selected(z) Then
With Sheets("Stakeholders")
.Range("B" & NextRow) = ActiveCell.Value
.Range("D" & NextRow) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 2)
.Range("E" & NextRow) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 3)
.Range("F" & NextRow) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 4)
.Range("G" & NextRow) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 5)
.Range("H" & NextRow) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 6)
.Range("I" & NextRow) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 7)
.Range("J" & NextRow) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 8)
.Range("K" & NextRow) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 9)
.Range("L" & NextRow) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 10)
.Range("M" & NextRow) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 11)
.Range("N" & NextRow) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 12)
.Range("O" & NextRow) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 13)
.Range("P" & NextRow) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 14)
.Range("Q" & NextRow) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 15)
.Range("R" & NextRow) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 16)
.Range("S" & NextRow) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 17)
NextRow = NextRow + 1
End With
With Sheets("Stakeholder_List")
NextRow2 = Range("D" & Rows.Count).End(xlUp).Row + 1
.Range("A" & NextRow2) = ActiveCell.Value
.Range("B" & NextRow2) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 2)
.Range("C" & NextRow2) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 3)
.Range("D" & NextRow2) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 4)
.Range("E" & NextRow2) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 5)
.Range("F" & NextRow2) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 6)
.Range("G" & NextRow2) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 7)
.Range("H" & NextRow2) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 8)
.Range("I" & NextRow2) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 9)
.Range("J" & NextRow2) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 10)
.Range("K" & NextRow2) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 11)
.Range("L" & NextRow2) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 12)
.Range("M" & NextRow2) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 13)
.Range("N" & NextRow2) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 14)
.Range("O" & NextRow2) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 15)
.Range("P" & NextRow2) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 16)
.Range("Q" & NextRow2) = Worksheets("Stakeholder_List").Cells.Item(lstResults2.List(lstResults2.ListIndex, 3), 17)
End With
End If
Next z
Unload Me
End Sub

[Aflatoon]

The first check is to see if you have selected something - if there are no list items, nothing will be selected, so that's the message you will see.

[mardy_bum]

Hi Aflatoon,

Practically I guess I can do away with the first error message as this is handled in another control, however I still do not understand why it doesnt work when it should?? If you could spread any light on whats going wrong I'd appreciate it!

my first error check

If lstResults2.ListIndex <> -1 Then
checks to make sure that there is something in the list, otherwise a messagebox displays (which is the only one displaying)
The problem is when a search has been carried out and the list box is populated with results but nothing has been selected, which I have this line of code for

With lstResults2
For y = 0 To .ListCount - 1
If .Selected(y) Then NumSelected = NumSelected + 1
Next y
End With
If NumSelected <> 0 Then
So it should display the second error message, but it displays the first!

[Kenneth Hobs]

The value of ListIndex will depend on the selection and the type of MultiSelect property you used for the Listbox control.

It might be better to use ListCount property for the first part.

Dim variables with a row value as Long.


Private Sub CommandButton1_Click()
MsgBox ListBox1.ListCount
MsgBox ListBox1.ListIndex
End Sub