Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error message when searching for an item not in a listbox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error message when searching for an item not in a listbox
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching for an additional item via an IF statement | Excel Worksheet Functions | |||
move item from one listbox to another listbox | Excel Programming | |||
Remove Item from Listbox by Item Name | Excel Programming | |||
Double click item in Listbox to select item and close Listbox | Excel Programming | |||
Identifying an item in one col/row and searching an entire spreads | Excel Worksheet Functions |