Lookup Function in user form

  • Thread starter Thread starter Rachel
  • Start date Start date
R

Rachel

I have a combo box (cboitem1) and a text box (txtprice1). I am using the code
below to have txtprice1 fill with the corresponding price the worksheet:

Private Sub Cboitem1_Change()
If cboItem1 <> "" Then
txtprice1.Value = Excel.WorksheetFunction.VLookup(cboItem1.Value,
Sheets("Pizzas").Range("A1:B65536"), 2, False)
End If
End Sub

It works perfectly when you enter an existing value in cboitem1. However if
'the user' accidently types a letter that is not the first letter of one of
the existing items VB debugging pops up with error "Unable to get the Vlookup
property..."

I am assuming this is because the Vlookup function is giving afalse value.
How can I get it to not revert to the debugging and just give an error
message?

Thanks in advance!
R
 
Rachel,
see if this approach works for you.
I have assumed Item is in Col 1 (A) & Price in Col 2 (B) change as required.

Private Sub Cboitem1_Change()
Dim Foundcell As Range
Dim Search As String

Search = Cboitem1.Text

If Cboitem1 <> "" Then

With ThisWorkbook.Worksheets("Pizzas")

Set Foundcell = .Columns(1).Find(Search, _
After:=.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

End With

If Foundcell Is Nothing = False Then

txtprice1.Text = Foundcell.Offset(0, 1).Value

End If

End If

End Sub
 
Thank you John, works great!


john said:
Rachel,
see if this approach works for you.
I have assumed Item is in Col 1 (A) & Price in Col 2 (B) change as required.

Private Sub Cboitem1_Change()
Dim Foundcell As Range
Dim Search As String

Search = Cboitem1.Text

If Cboitem1 <> "" Then

With ThisWorkbook.Worksheets("Pizzas")

Set Foundcell = .Columns(1).Find(Search, _
After:=.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

End With

If Foundcell Is Nothing = False Then

txtprice1.Text = Foundcell.Offset(0, 1).Value

End If

End If

End Sub
Click to expand...
 
One other thing - if the user clears all the entry from cboitem1 the price
stays in txtprice1. Can it be cleared as well. I tried putting a blank row at
the top of the worksheet where the data is so A1 and B1 are both blank. But
this doesn't work....

Thanks again.
 
Hi Rachel,
thought about that after I posted.
Following should do what you want & also added bit extra to make backcolor
red if invalid - you can modify as required.

If you need any further help contact me directly nospamdt @ btinternet .com
(close spaces)

Private Sub Cboitem1_Change()
Dim Foundcell As Range
Dim Search As String

Search = Cboitem1.Text

If Search <> "" Then

With ThisWorkbook.Worksheets("Pizzas")

Set Foundcell = _
.Columns(1).Find(Search, _
After:=.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

End With

If Foundcell Is Nothing = False Then

With txtprice1

.Text = Format(Foundcell.Offset(0, 1).Value, "£0.00")

'make backcolor white
.BackColor = &HFFFFFF

End With

Else

With txtprice1

.Text = ""

'make backcolor red
.BackColor = &HFF&

End With

End If

End If

End Sub
 
Back
Top