mardy_bum
09-02-2015, 04:12 AM
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
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