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


On 23 Dec., 03:51, Roger on Excel
wrote:
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:=lstChemicalDa tabase.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