Return text that is different from combo box text

Question

I am setting up a combo box to update a pivot table.

I need the value the combo box returns to be different from the selected text.

For example. You select a product's name in the drop down box, "Cheerios". It has a SKU number of 1234. I need the combo box to return the 1234.

Edit:

Below is an image of where I am getting my list populated from. Column B is what is being displayed in the drop down, column A is what I need returned.

Edit 2:

Private Sub cmb_SkuSelect_Click()
    Dim xlSheetSort As Worksheet
    Dim lastRow As Long
    Dim skuValue As Integer

    Set xlSheetSort = ActiveWorkbook.Worksheets("Sort")
    lastRow = xlSheetSort.Range("A1").End(xlDown).Row

    With xlSheetSort.Range("B1:B" & lastRow)
        Set c = .Find(cmb_SkuSelect.Value, LookIn:=xlValues)
        If Not c Is Nothing Then
            skuValue = xlSheetSort.Range("A" & c.Row).Value
        End If
    End With

    cmb_SkuSelect.Value = ""
    ActiveWorkbook.ActiveSheet.Range("A4").Value = skuValue

    updatePivot skuValue
End Sub

updatePivot:

Public Sub updatePivot(ByVal sku As Integer)
    Dim xlSheet As Worksheet
    Dim xlPTable As PivotTable

    Set xlSheet = ActiveWorkbook.Worksheets("Sku Inventory")

    For Each xlPTable In xlSheet.PivotTables
        With xlPTable
            .PivotFields("Sku Number").CurrentPage = sku
        End With
    Next
End Sub

Answer 1

Try that:

Private Sub ComboBox1_Change()
    Dim valueToLook As String
    valueToLook = ComboBox1.Value
    Dim sku, i As Integer
    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With
    For i = 1 To LastRow
        If Cells(i, 2).Value = valueToLook Then
            sku = Cells(i, 1).Value
            MsgBox sku
            Exit For
        End If
    Next i
End Sub

Answer 2

Yes, it's definitely possible - all you need to do is make the combobox a multi-column one, set its BoundColumn to the SKU Number's column and hide that column afterwards. It's a late answer, but it solves your problem.

I'm not going to bother with your existing structure, but rather present a simple example that you can adapt to your needs afterwards. Assuming you already have the form (e.g. UserForm1) and the combobox (e.g. ComboBox1) in a standard workbook, paste this into your userform module:

Private Sub ComboBox1_Change()
    ' Show the expected result to the user
    MsgBox Me.ComboBox1.Value
End Sub

Private Sub UserForm_Initialize()
  Dim cbitems(2, 1) As Variant
    ' Set the number of columns in the combobox to 2 (i.e. Product Name and SKU Number)
    Me.ComboBox1.ColumnCount = 2
    ' Set the 2-nd column's value to be used as the value of the combobox (i.e. the SKU Number)
    Me.ComboBox1.BoundColumn = 2
    ' Set the 2-nd column's width to 0, hiding the column (i.e. only the Product Name is visible)
    Me.ComboBox1.ColumnWidths = ";0"
    ' Populate a 2D array with the values of the combobox columns
    cbitems(0, 0) = "Cheerios"
    cbitems(0, 1) = "1234"
    cbitems(1, 0) = "Apples"
    cbitems(1, 1) = "1672"
    cbitems(2, 0) = "Peaches"
    cbitems(2, 1) = "3722"
    ' Populate the combobox with the above array, using the List method (i.e. not the AddItem one)
    Me.ComboBox1.List = cbitems
    ' Set the 1-st item of the combobox to be its default one
    Me.ComboBox1.ListIndex = 0
End Sub

The comments explain what happens. All you have to do is adapt the code to your usage scenario (e.g. you'll probably want to populate the array/combobox using a loop, maybe move the relevant code to a different Sub than the userform's Initialize event, potentially add column headers if you want both columns to be visible, etc.)

Note: The Change event of the combobox will fire a couple of times when the combobox is first drawn on the userform, so you might get a few empty message-boxes at the start. Don't mind them, the important ones are those after you select some item from the list later on. The message boxes are just for convenience anyway, and, along with the last line in the userform's Initialize event, they can be safely removed from the code once you get the idea.