VBA Help populating combobox from unsorted list

[DJMXM]

Hello Everyone.... I am here once again working on this same problem for the 3 day in a row now. Here is the issue I have. I have built a Userform that is for a Liquor Inventory. I would like to scan each bottle's UPC Code and pull it from a range list that is set up in (Combobox "UpcCodeBox"). That information is stored on a sheet "Liquor & Wine Inventory" in Column "A" (Unsorted). Column "B" is the name of the liquor which is also the column that everything is sorted in alphabetical order. What I would like to do is populate several textbox's that I have set up so that I can input missing data or data that needs to be updated. The problem is I would LOVE to keep the list sorted by Liquor Names (Column "B") and not by UPC Bar Codes (Column "A"). From my research I have found that Index and Match might be the way to go but I can't seem to get it to work right. Since I am still learning VBA, Some things still escape my grasp. Please Help!!! I have posted the UserForm Code below for you to look over. Thank You in advance for your help!!

Mike

Private Sub UserForm_Initialize()
    Me.UpcBarCodeBox.List = Worksheets("Liquor & Wine Inventory").Range("A5:A205").Value
        With ComboBox2
            .AddItem "0"
            .AddItem "1"
            .AddItem "2"
            .AddItem "3"
            .AddItem "4"
        End With
End Sub

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If TextBox3 = vbNullString Then Exit Sub
            If IsNumeric(Me.TextBox3.Value) Then
        Me.TextBox3.Value = Format(Me.TextBox3.Value, "Currency")
    End If
End Sub

Private Sub TextBox_Exit()
    TextBox1.Value = Format(TextBox1.Value, "#,##0.000")
End Sub

Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me
        If IsEmpty(.TextBox4) Then Exit Sub
        .TextBox6.Value = (Val(.TextBox4.Value) + Val(.TextBox5.Value)) * 1
    End With
End Sub

Private Sub UpcBarCodeBox_Change()
Application.ScreenUpdating = False
Dim wsSource As Worksheet
Set wsSource = Worksheets("Liquor & Wine Inventory")
wsSource.Activate
    LiquorInventory.LiquorName.Value = WorksheetFunction.VLookup(UpcBarCodeBox.Value, Range("A5:Z205"), 2)
    LiquorInventory.TextBox1.Value = WorksheetFunction.VLookup(UpcBarCodeBox.Value, Range("A5:Z205"), 3)
    LiquorInventory.TextBox2.Value = WorksheetFunction.VLookup(UpcBarCodeBox.Value, Range("A5:Z205"), 5)
    LiquorInventory.TextBox3.Value = WorksheetFunction.VLookup(UpcBarCodeBox.Value, Range("A5:Z205"), 6)
    LiquorInventory.TextBox4.Value = WorksheetFunction.VLookup(UpcBarCodeBox.Value, Range("A5:Z205"), 10)
End Sub

Private Sub ClearButton_Click()
    Call UserForm_Initialize
End Sub

Private Sub CancelButton_Click()
    Unload Me
End Sub

 

[bertie]

In the initialize event you populate the ComboBox from a given range on the worksheet A5:A205.

This creates a ListIndex of the entries in the Combobox:
ListIndex 0 = A5 value
ListIndex 1 = A6 value, etc

You can, therefore, use the ListIndex property of the ComboBox;
add 5, because your data starts in A5;
and this will give you the row the record you are processing is on.

You can assign this row to a variable and use it to populate controls, or write back to the worksheet.

See the sample Change_Event below.

Private Sub UpcBarCodeBox_Change()
   Dim rw As Long 'row the current record is on
   
   'use the ListIndex property of the ComboBox
   'add 5 because your data starts at A5
   rw = Me.UpcBarCodeBox.ListIndex + 5
   
   With Worksheets("Liquor & Wine Inventory")
      LiquorInventory.LiquorName.Value = .Range("A" & rw).Value
      LiquorInventory.TextBox1.Value = .Range("B" & rw).Value
      LiquorInventory.TextBox2.Value = .Range("C" & rw).Value
      LiquorInventory.TextBox3.Value = .Range("D" & rw).Value
      LiquorInventory.TextBox4.Value = .Range("E" & rw).Value
   End With
End Sub

Additional Reading
See here for disabling events from triggering in UserForms when writing back to worksheets.
Suppressing Events In UserForms

 

[DJMXM]

Bertie..... Thank you for your help.... I will try this when I get back to my office.... One question, am I still populating my combobox with the original code?..... I am still having issues filling that combobox because column "A" is an unsorted list of UPC numbers.... The page is sorted by column "B" (Liquor List) .

Mike

 

[bertie]

You could sort the data on column B.
Then populate the ComboBox with the Liquor Names form column B.

Then all you need to do is determine the row the information is on using the ListIndex as above
And use the row variable to populate form controls or sheets.