Lookup Function in user form
 

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

Lookup Function in user form
 

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


"Rachel" wrote:

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

Lookup Function in user form
 

Thank you John, works great!


"john" wrote:

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


"Rachel" wrote:

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

Lookup Function in user form
 

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.

"john" wrote:

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


"Rachel" wrote:

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

Lookup Function in user form
 

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


"Rachel" wrote:

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.

"john" wrote:

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


"Rachel" wrote:

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