R
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
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