ListBox Condition Not working for 1st Entry
 

I have a form with 2 Listboxes
Once a Value is selected from Listbox1, Listbox2 is populated with a value(offset) to the Listbox1 sheet value.

When the user selects the Listbox2 value the below code runs, but when there is 1 entry matching if the user clicks the 1st entry the code does not work. But if the 2nd is chosen the code works.
The code simply places the required data into another sheet(sheet6).


Private Sub ListBox2_Click()
Application.ScreenUpdating = False
With Worksheets("Data")
..Select
Dim rngFound As Range
On Error Resume Next
Sheets("Data").Unprotect
With Worksheets("Data").Range("B:B")
Set rngFound = .Find(What:=ListBox1.Value, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, Matchbyte:=False)
If rngFound.Value < "" And rngFound.Offset(0, -1).Text = ListBox2.Text Then ' <== Condition to ensure the value in Column A was chosen in Listbox1
With Sheet6
.Select
.Range("A100") = rngFound.Value ' <== When both Listbox1 & Listbox2 value are correct, the Listbox1 value(rngFound) is placed into Sheet6.Range("A100")
End With
End If
End With
..Protect
End With
Sheet6.Activate
Application.ScreenUpdating = True
End Sub


I do not understabd WHY the 1st Instance of the values in Listbox2 will NOT place data to Sheet6.

But the 2nd instance will????


--
Corey ....
The Silliest Question is generally
the one i forgot to ask.

ListBox Condition Not working for 1st Entry
 

I added code for debugging

Set rngFound = .Find(What:=ListBox1.Value, After:=.Cells(1),
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious, MatchCase:=False, Matchbyte:=False)
if rngFound is Nothing then
Msgbox("Did not find : " & ListBox1.Value & " - Exiting Macro")
exit sub
end if
If rngFound.Value < "" And rngFound.Offset(0, -1).Text = ListBox2.Text
Then ' <== Condition to ensure the value in Column A was chosen in Listbox1


"Corey" wrote:

I have a form with 2 Listboxes
Once a Value is selected from Listbox1, Listbox2 is populated with a value(offset) to the Listbox1 sheet value.

When the user selects the Listbox2 value the below code runs, but when there is 1 entry matching if the user clicks the 1st entry the code does not work. But if the 2nd is chosen the code works.
The code simply places the required data into another sheet(sheet6).


Private Sub ListBox2_Click()
Application.ScreenUpdating = False
With Worksheets("Data")
..Select
Dim rngFound As Range
On Error Resume Next
Sheets("Data").Unprotect
With Worksheets("Data").Range("B:B")
Set rngFound = .Find(What:=ListBox1.Value, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, Matchbyte:=False)
If rngFound.Value < "" And rngFound.Offset(0, -1).Text = ListBox2.Text Then ' <== Condition to ensure the value in Column A was chosen in Listbox1
With Sheet6
.Select
.Range("A100") = rngFound.Value ' <== When both Listbox1 & Listbox2 value are correct, the Listbox1 value(rngFound) is placed into Sheet6.Range("A100")
End With
End If
End With
..Protect
End With
Sheet6.Activate
Application.ScreenUpdating = True
End Sub


I do not understabd WHY the 1st Instance of the values in Listbox2 will NOT place data to Sheet6.

But the 2nd instance will????


--
Corey ....
The Silliest Question is generally
the one i forgot to ask