Thread: VLookup to populate useform textbox

Hi,

I'm trying to use the following code to populate a textbox in a userform based on the selection made in a combobox. The "MerchPrices" in the the code is a defined name for a two column selection. The first column fills the combobox and the second is intended to fill the textbox.

Private Sub Combobox1_Change()

Textbox1.Locked = False
Textbox1.Value = WorksheetFunction.VLookup(CLng(Combobox1.Value), Range("MerchPrices"), 2, 0)
Textbox1.Locked = True

End Sub

When I run the code I get a "Run-time error '13': Type mismatch". I don't know how to troubleshoot this problem. The array that fills the combobox with items matches up with the "MerchPrices" array, so I don't think there is a problem with the arrays mismatching.


Thanks,

Hello Einstahb,

The problem maybe you are not using the function correctly. For example, let's say the 2-D table has 2 columns. The first (left most) is "Item" and the second is "Price" and table looks like this and is named "Current_Pricing".

Item    Price
Item A  $12.50
Item B  $7.95
Item C  $14.00
Item D  $3.75
Item E  $5.25

The function must match the look up value in the "Item" column. When a match is found, the column index (third argument) will return the value from that column in the same row as a match. If you have...

    Price = WorksheetFunction.VLookup("Item C", Range("Current_Pricing", 2, 0)

The price returned will $14.00 for Item C.

I wouldn't use a Lookup. You might also consider a Label instead of a TextBox, then you don't need to Lock/Unlock. This code assumes the list has a header row in Row 1. The ListIndex starts at zero so adding 2 allows for the header Row & gets the row number that corresponds

Option Explicit


Private Sub Combobox1_Change()
    With Me
        .TextBox1.Locked = False
        .TextBox1.Value = Sheet1.Range("MatchPrices").Cells(.ComboBox1.ListIndex + 2, 2).Value
        .Label1.Caption = Sheet1.Range("MatchPrices").Cells(.ComboBox1.ListIndex + 2, 2).Value
        .TextBox1.Locked = True
    End With
End Sub


Private Sub UserForm_Initialize()
    Me.ComboBox1.List = Sheet1.Range("MatchPrices").Columns(1).Offset(1, 0).Value
    Me.TextBox1.Locked = True
End Sub

Try this

Option Explicit


Private Sub Combobox1_Change()
    With Me
        .TextBox1.Locked = False
        .TextBox1.Value = Sheet1.Range("MatchPrices").Cells(.ComboBox1.ListIndex + 2, 2).Value
        .Label1.Caption = format(Sheet1.Range("MatchPrices").Cells(.ComboBox1.ListIndex + 2, 2).Value,,"Currency"))
        .TextBox1.Locked = True
    End With
End Sub


Private Sub UserForm_Initialize()
    Me.ComboBox1.List =Sheet1.Range("MatchPrices").Columns(1).Offset(1, 0).Value
    Me.TextBox1.Locked = True
End Sub

Once the Userform is loaded, I wouldn't refer to the sheet at all

Private Sub ComboBox1_Change()
    With ComboBox1
        If -1 < .ListIndex Then TextBox1.Text = .Value
    End With
End Sub

Private Sub UserForm_Initialize()
    With ComboBox1
        .ColumnCount = 2
        .ColumnWidths = ";0"
        .TextColumn = 1
        .BoundColumn = 2
        .List = Range("MatchPrices").Resize(,2).Value
    End With
End Sub

There isn't any great advantage in this application, where the whole range is loading into the ComboBox.
But the approach is most useful when one fills a combobox with filtered items from a data set. (e.g. all the items that are in stock). If you have a fiddely filter for the data base, then all the fiddling is concentrated in one place in the code, the Load Combobox section, and doesn't propegate to the rest of it.