CODEWITHSUNDEEP
excelvbacomboboxuserform
First Last
First Last

Reputation: 145

Find and select the item found in the combobox list

In the code below, I check if the value of Sheet2 cell A1 is contained in combobox1 list and, if found, put it in the 'selection mode'. But it does not work. Which part of the code should be corrected?

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

Private Sub CommandButton1_Click()
  Dim foundRng As Range
  Set findrange = Sheets("Sheet1").Range("A1:B5")
  Set foundRng = findrange.Find(Sheets("Sheet2").Range("A1"))

  If foundRng Is Nothing Then
    MsgBox "Nothing found"
  Else
    MsgBox "I Found"
    Me.ComboBox1.ListIndex = foundRng.Value
  End If
End Sub

Upvotes: 1

Views: 449

Answers (1)

T.M.
T.M.

Reputation: 9948

Declare variables and provide for correct data types

I didn't change your code too much, but would like to give you some hints:

  1. Set Option Explicit to compel yourself to declare variables (objects).
  2. Provide for input cases in your Sheet2!A1 cell where a type mismatch could occur if you compare a string or an empty string (and not a number) against ListIndex numbers.
  3. It's recommended to fully qualify your range references (fqrr).
  4. Prefer to use the term Worksheets if you are referring to worksheets only.
  5. Check Stack Overflow's Help Tour regarding How do I ask a good question?, and, How to create a Minimal, Complete, and Verifiable example
  6. Try to learn something about error handling and Debugging VBA in order to be in the position to give more precise information about occurring errors. "It doesn't work" is like a red rag for a bull to more experienced programmers at this site, be more precise here :-;

Some minor changes ...

Option Explicit      ' declaration head of your UserForm code module
Dim xrg As Range     ' possibly declared here to be known in all UserForm procedures

Private Sub UserForm_Initialize()
Set xrg = ThisWorkbook.Worksheets("Sheet1").Range("A1:B5")  ' << fully qualified range reference (fqrr)
Me.ComboBox1.List = xrg.Columns(1).Value
End Sub

Private Sub CommandButton1_Click()
Dim foundRng As Range, findrange As Range
Set findrange = ThisWorkbook.Worksheets("Sheet1").Range("A1:B5") ' fqrr
Set foundRng = findrange.Find(Thisworkbook.Worksheets("Sheet2").Range("A1")) ' fqrr
If foundRng Is Nothing Then
    MsgBox "Nothing found"
    Me.ComboBox1.ListIndex = -1
ElseIf foundRng.Value = vbNullString Then
    MsgBox "Empty search item"
    Me.ComboBox1.ListIndex = -1
Else
    MsgBox "1 item found"
    If IsNumeric(foundRng.Value) Then
       Me.ComboBox1.ListIndex = CLng(foundRng.Value) + 1
    Else
       Me.ComboBox1.ListIndex = foundRng.Row - 1
    End If
End If
End Sub

Recommended link

You can find a helpful guide about Debugging VBA at Chip Pearson's site.

Addendum due to comment

In order to define a dynamic range without following empty rows you could rewrite the Initialize procedure as follows:

Private Sub UserForm_Initialize()
Dim n&                           ' ... As Long 
With ThisWorkbook.Worksheets("Sheet1")
    n = .Range("A" & .Rows.Count).End(xlUp).Row
    Set xrg = .Range("A1:B" & n)  ' << fully qualified range reference 
End With
Me.ComboBox1.List = xrg.Columns(1).Value
End Sub

Good luck for future learning steps :-)

Upvotes: 1

Related Questions