Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help regarding ComboBox
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 he 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 -- Thanks in advance. Vikram P. Dhemare |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help regarding ComboBox
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. Vikram Dhemare wrote: 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 he 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 -- Thanks in advance. Vikram P. Dhemare -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help regarding ComboBox
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" wrote: 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. Vikram Dhemare wrote: 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 he 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 -- Thanks in advance. Vikram P. Dhemare -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill combobox depending on selection from another combobox | Excel Discussion (Misc queries) | |||
Combobox items determined by the selection in another combobox | Excel Programming | |||
Combobox options based on the input of another combobox | Excel Programming | |||
ComboBox list reliant on the entry from a different ComboBox | Excel Programming | |||
How Do I Load A ComboBox RowSource From The Results Of Another ComboBox | Excel Programming |