with push bottom If value cell = Combobox - Show in userform combobox - VBA

Question

I want to complete the code as follows: Pressing the "CommandButton1", if the cell is equal to the value of the "ComboBox1", is selected and displayed in the "ComboBox1".

My code (not complete):

Private Sub UserForm_Initialize()
Dim xRg As Range
Set xRg = Worksheets("LookupLists").Range("A1:B5")
Me.ComboBox1.List = xRg.Columns(1).Value
End Sub

Private Sub CommandButton1_Click()
If Sheets("Sheet1").Range("A1") = "" Then
    Beep
    Else
    If Sheets("Sheet1").Range("A1") = xRg Then
    'What code should I write here to if
    '"Sheets("Sheet1").Range("A1")=Worksheets("LookupLists").Range("A1:B5")" show the amount in comboBox1?
    End If
End If
End Sub

Answer 1

How to match a value in a comboboxes hidden 2nd column and highlight a found item

It wasn't that easy to understand what you actually wanted to do.

As you are referring to a two columns data range A:B but display only the first range column (possibly identifying e.g. names or IDs) in a combobox, I assume the following:

• After clicking the CommandButton1 control and
• under the condition that a given cell value (A1) equals the 2nd value column of ComboBox1 (possibly hidden),
• the corresponding ComboBox1 item (displaying e.g. names) should be selected (highlighted).

Applied method

The essential code line within the CommandButton1_Click event procedure is the assignment of a possible match position to the variable pos:

pos = Application.Match(myVal, Application.Index(Me.ComboBox1.List, 0, 2), 0)

As ►Application.Match needs a one dimensional array and ComboBox1.List is 2-dimensional, the Application.Index function has to extract the 2nd (hidden) combobox column (containing values) from the combo list thus getting a 1-dim array now as argument.

...Application.Index(Me.ComboBox1.List, 0, 2) ' c.f. Note *)

• ) Note that the row index in the Application.Index function above has to be set to 0, so that the column index ►2 can isolate the 2nd column only.

If a match position has been found, the variable pos receives the numeric item number which can be used to select a found item by resetting the Comboboxes .ListIndex (subtracting 1 as the box indices are zero based).

If no corresponding value has been found in the combobox, pos gets an error returned and it's therefore necessary to avoid an error message by checking this variable via the ►IsError() function (this is why pos has to be declared as Variant and not only as Long to avoid type mismatches errors.)

UserForm code

Option Explicit

Private Sub UserForm_Initialize()
Dim xrg   As Range
Set xrg = ThisWorkbook.Worksheets("LookupLists").Range("A1:B5")
With Me.ComboBox1
    .List = xrg.Value2
    .ColumnCount = 2            ' << provide for data of both columns
    .ColumnWidths = .Width      ' << show only first column in full box width
End With

End Sub

Private Sub CommandButton1_Click()
Dim pos As Variant                      ' item position has to be variant to allow IsError()
Dim myVal
myVal = ThisWorkbook.Worksheets("Sheet1").Range("A1")
If myVal = "" Then
   Beep
Else                                    ' try to get a match and its position
   pos = Application.Match(myVal, Application.Index(Me.ComboBox1.List, 0, 2), 0)
   If IsError(pos) Then pos = 0          ' avoid error msg if not found :-)
   Me.ComboBox1.ListIndex = pos - 1      ' goto item no: index is zero based!
End If
End Sub