Error message when searching for an item not in a listbox

[Roger on Excel]

I use a listbox on a userform to display a chemical database.

I use the following code to search for an item (entered into
txtFindChemical). The code finds the chemical in the database and populates
text boxes with the chemicals information

Private Sub cmdFindChemical_Click()
Dim Chemical As Variant
lstChemicalDatabase.Value = txtFindChemical.Value

Chemical = Sheets("Chemicals").Cells.Find(What:=lstChemicalDatabase.Value,
LookIn:=xlFormulas, _
LookAt:=1, SearchDirection:=xlNext, MatchCase:=False).Offset(0,
0).Address

With Sheets("Chemicals").Range(Chemical)
TextBox1.Value = .Offset(0, 0)
TextBox2.Value = .Offset(0, 1)
TextBox3.Value = .Offset(0, 2)
TextBox4.Value = .Offset(0, 3)
TextBox5.Value = .Offset(0, 4)
TextBox6.Value = .Offset(0, 5)
End With
End Sub

The code works fine, but breaks down when the searched for chemical isnt in
the database.

The following error mesage comes up :"Run Time error 380 Could not set the
value property. Invalid Property value"

Is there a way to add some code which recognizes this error and alerts the
user with a msgbox "Chemical Not Found" so that the code doesnt breakdown

Many Thanks,

Roger

 

[Per Jessen]

Hi Roger

Try this:

Private Sub cmdFindChemical_Click()
Dim Chemical As Range
lstChemicalDatabase.Value = txtFindChemical.Value

Set Chemical = Sheets("Chemicals").Cells.Find
(What:=lstChemicalDatabase.Value, LookIn:=xlFormulas, _
LookAt:=1, SearchDirection:=xlNext,
MatchCase:=False).Offset(0, 0).Address
If Chemical Is Nothing Then
msg = MsgBox("Chemical not found", vbExclamation + vbOKOnly)
Else
With Chemical
TextBox1.Value = .Offset(0, 0)
TextBox2.Value = .Offset(0, 1)
TextBox3.Value = .Offset(0, 2)
TextBox4.Value = .Offset(0, 3)
TextBox5.Value = .Offset(0, 4)
TextBox6.Value = .Offset(0, 5)
End With
End If
End Sub

Regards,
Per