Need help regarding ComboBox

V

Vikram Dhemare

Dear All,

I have userform. As soon as the user selects the value from combobox the
another test box lookup the value from certain table & returns the result in
textbox. (Here I used the vlookup function).
The combobox list is having alphanumerical value as well as numerical value.
If user selects the alphanumerical value from combobox, it working fine. But
if user selects the numerical value, it doesn't work.The Vlookup function
fails here.
Any idea?

Codes are here:

Private Sub cboPart_Change()
Dim LookupRange As Range
On Error Resume Next
Err.Clear
Set LookupRange = Worksheets("LookupLists").Range("A:I")

If cboPart <> "" Then
Me.LblDesc = Application.WorksheetFunction.VLookup _
(Me.cboPart.Value, LookupRange, 3, 0)

If Err.Number <> 0 Then
MsgBox "An Error occurred ! Plz. check the Part Code!"
Exit Sub
End If
End If
End Sub
 
D

Dave Peterson

I think you have at least a couple of options.

You could check to see if the part number is numeric:
if isnumeric(Me.cboPart.Value) then
'do the lookup using cdbl(Me.cboPart.Value)

or you could do it twice (untested):

Option Explicit
Private Sub cboPart_Change()
Dim LookupRange As Range
Dim res As Variant

Set LookupRange = Worksheets("LookupLists").Range("A:I")

If cboPart <> "" Then
res = Application.VLookup(Me.cboPart.Value, LookupRange, 3, 0)
If IsError(res) Then
'look as a number:
res _
= Application.VLookup(CDbl(Me.cboPart.Value), LookupRange, 3, 0)
End If

If IsError(res) Then
MsgBox "An Error occurred ! Plz. check the Part Code!"
Else
Me.LblDesc = res
End If
End If
End Sub

But I think I would use a third option.

I'd change my table to be text in column A, then just look for text.
 
V

Vikram Dhemare

Thanks a lot Mr. Dave. Its working (Second option)
Since last 3 weeks I was looking for the solution but couldn't get.
Thanks again.

--
Thanks,
Vikram P. Dhemare


Dave Peterson said:
I think you have at least a couple of options.

You could check to see if the part number is numeric:
if isnumeric(Me.cboPart.Value) then
'do the lookup using cdbl(Me.cboPart.Value)

or you could do it twice (untested):

Option Explicit
Private Sub cboPart_Change()
Dim LookupRange As Range
Dim res As Variant

Set LookupRange = Worksheets("LookupLists").Range("A:I")

If cboPart <> "" Then
res = Application.VLookup(Me.cboPart.Value, LookupRange, 3, 0)
If IsError(res) Then
'look as a number:
res _
= Application.VLookup(CDbl(Me.cboPart.Value), LookupRange, 3, 0)
End If

If IsError(res) Then
MsgBox "An Error occurred ! Plz. check the Part Code!"
Else
Me.LblDesc = res
End If
End If
End Sub

But I think I would use a third option.

I'd change my table to be text in column A, then just look for text.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top