Dynamisk Search List DropDown in a Userform, ComboBox

gnaske · Nov 2nd 2015

Hi Forum.

I found a Dynamic Search ComboBox video at the net, which show a dynamic list of all matching records, meanwhile they are typed.
But the ComboBox is an In-Sheet ComboBox.

Video link click here

What I need instead is an In-Userform ComboBox and I tried to copy the codes into a Userform, but get a BUG in the NameRange List code.
And then I think some settings is wrong in the ComboBox settings too, but I'm not sure.

I had tried different forums, both Danish and English, but no solutions was send back.
Maybe it's too difficult or not possible to make this dynamic list in a In-Userform, ComboBox, but I at least hope to get some response here.

Any suggestions how to make the In-Userform ComboBox Dynamic ?
I use Excel 2010.

I'm not very good in VBA coding and will ask if it's possible to make the change in the attached file and send it back to me with an explanation.
Then I'll take a look at it.

Thank you in advance.

Ib

**Kenneth Hobson** · Nov 2nd 2015

Re: Dynamisk Search List DropDown in a Userform, ComboBox

I guess the main thing is to modify your Find in your Module to only find from Column C.

In the Userform I used the List property.

Code

Private Sub UserForm_Initialize()
    'ComboBox1.RowSource = "Ark1!C2:C" & Range("C" & Rows.Count).End(xlUp).Row
    ComboBox1.List = WorksheetFunction.Transpose( _
      Worksheets("Ark1").Range("C2", Worksheets("Ark1").Range("C" & Rows.Count).End(xlUp)))
End Sub


Private Sub ComboBox1_Change()
    Sheets(1).Select
    Application.Goto "R2C3"
    
    Call Module1.Update
End Sub


Private Sub CommandButton1_Click()
    Unload Me
End Sub

Display More

In the Module:

Code

Sub Update()
    Dim ws As Worksheet
    Dim match As Range
    Dim findMe As String
    Dim findOffset As String


    Set ws = ThisWorkbook.Sheets(1)
    findMe = UserForm1.ComboBox1.Value


'*'Find text to the right
    Set match = Worksheets("Ark1").Range("C2", Worksheets("Ark1").Range("C" & Rows.Count).End(xlUp)).Find(findMe)
    If match Is Nothing Then Exit Sub
    
'*'Show text to the right - In the userform.textboxes
    UserForm1.TextBox1.Value = match.Offset(, 1).Value
    UserForm1.TextBox2.Value = match.Offset(, 2).Value
    UserForm1.TextBox2.Value = Format(UserForm1.TextBox2.Value, "d. mmmm yyyy") ' Change date of birth to Danish date format
    UserForm1.TextBox3.Value = match.Offset(, 3).Value
    UserForm1.TextBox4.Value = match.Offset(, 4).Value
    UserForm1.TextBox5.Value = match.Offset(, 5).Value
    
    Sheets(1).Range("B2").Value = UserForm1.ComboBox1.Value
End Sub

Display More

gnaske · Nov 3rd 2015

Re: Dynamisk Search List DropDown in a Userform, ComboBox

Hi Kenneth.

I copied both the Userform and Module codes into my project, but got at BUG in the Sheet(1).CommandButton, UserForm1.Show, when trying to open the Userform.

Then I took one code at the time.

First the module code and it didn't made a BUG

Code

'*'Find text to the right
'Set match = ws.Cells.Find(findMe)


       Set match = Worksheets("Ark1").Range("C2", Worksheets("Ark1").Range("C" & Rows.Count).End(xlUp)).Find(findMe)
       If match Is Nothing Then Exit Sub

Next the Userform code, which made the BUG.

Code

'ComboBox1.RowSource = "Ark1!C2:C" & Range("C" & Rows.Count).End(xlUp).Row


ComboBox1.List = WorksheetFunction.Transpose( _
    Worksheets("Ark1").Range("C2", Worksheets("Ark1").Range("C" & Rows.Count).End(xlUp)))

I don't know if you tested the codes before posting them, but can it be because I use Excel 2010 and you maybe 2013 ?

Ib

jindon · Nov 3rd 2015

Re: Dynamisk Search List DropDown in a Userform, ComboBox

I've just done a similar thing

Code

Option Explicit
Private dic As Object


Private Sub UserForm_Initialize()
    Dim a, i As Long, ii As Long, w
    a = Sheets("ark1").[c1].CurrentRegion.Value
    ReDim w(1 To 5)
    Set dic = CreateObject("Scripting.Dictionary")
    ComboBox1.RowSource = ""
    For i = 2 To UBound(a, 1)
        For ii = 1 To 5
            w(ii) = a(i, ii + 1)
        Next
        dic(a(i, 1)) = w
    Next
    Me.ComboBox1.List = dic.keys
End Sub


Private Sub ComboBox1_Change()
    Dim e, AL As Object, i As Long
    Set AL = CreateObject("System.Collections.ArrayList")
    ClearTB 1, 5
    With Me.ComboBox1
        If .Value <> "" Then
            If dic.exists(.Value) Then
                .List = Array(.Value)
            Else
                For Each e In dic
                    If UCase$(e) Like UCase$(.Value) & "*" Then AL.Add e
                Next
                If AL.Count Then
                    .List = AL.ToArray
                    .DropDown
                Else
                    .List = dic.keys
                End If
            End If
            If .ListIndex > -1 Then
                For i = 1 To 5
                    Me("textbox" & i).Value = dic(.Value)(i)
                Next
            End If
        Else
            .List = dic.keys
        End If
    End With
End Sub


Private Sub CommandButton1_Click()
    Unload Me
End Sub


Private Sub ClearTB(s, e)
    Dim i As Long
    For i = s To e
        Me("textbox" & i).Value = ""
    Next
End Sub

Display More

gnaske · Nov 3rd 2015

Re: Dynamisk Search List DropDown in a Userform, ComboBox

Hi Jindon.

Hmmm - Tried to copy your code into my project, but it doesn't make the ComboBox-Search dynamic.

If you open my project and try to type the letter h in the In-Sheet ComboBox, you'll get 11 dynamic results in the dropbox, all containing the letter h.
- Claus Hansen
- Peter Henrik Poulsen
- Kevin Hansen
- Ole Hermansen
- Knud Aage Hansen
- Morten Christensen
- Claus Rydahl
- Jonathan Pedersen
- Ibrahim Kücakavci
- Christian Pedersen

If you then type the letter a, you'll reduce the result to 4 names containing the letters ha.
- Claus Hansen
- Kevin Hansen
- Knud Aage Hansen
- Jonathan Pedersen

If you type hans, you'll get the 3 names, all ending at the surname Hansen.
- Claus Hansen
- Kevin Hansen
- Knud Aage Hansen

This is what I wish the In-Userform ComboBox could do and hope it's possible :idea:

Ib

jindon · Nov 3rd 2015

Re: Dynamisk Search List DropDown in a Userform, ComboBox

I've just done for UserForm not combobox on the sheet.
And just chage

Code

For Each e In dic 
                    If UCase$(e) Like UCase$(.Value) & "*" Then AL.Add e 
                Next

to

Code

For Each e In dic 
                    If UCase$(e) Like "*" & UCase$(.Value) & "*" Then AL.Add e 
                Next

gnaske · Nov 3rd 2015

Re: Dynamisk Search List DropDown in a Userform, ComboBox

Wouw Jindon - It seems to work, but to be honest, I understand very little of the codes you made.

I can see the ComboBox-list is taken from Column A and not from Column C.

I also see in the Userform_Initialize, you define a = Sheets("ark1").[c1].CurrentRegion.Value
- I don't know if this is the "not working code" ?
- And if the [c1] is the right value, because the first name is in cell C2 ?

Code

Private Sub UserForm_Initialize()
    Dim a, i As Long, ii As Long, w
a = Sheets("ark1").[c1].CurrentRegion.Value
    ReDim w(1 To 5)
    Set dic = CreateObject("Scripting.Dictionary")
    ComboBox1.RowSource = ""
    For i = 2 To UBound(a, 1)
        For ii = 1 To 5
            w(ii) = a(i, ii + 1)
        Next
        dic(a(i, 1)) = w
    Next
    Me.ComboBox1.List = dic.keys
End Sub

Display More

I need to go from my computer now, but will be back later today.

Ib

jindon · Nov 3rd 2015

Re: Dynamisk Search List DropDown in a Userform, ComboBox

If you are taking about different workbook other than the one I uploaded, you may need to adjust the code.

If you can't, I need to see your workbook with the same structure as your actual workbook.

**Kenneth Hobson** · Nov 3rd 2015

Re: Dynamisk Search List DropDown in a Userform, ComboBox

For my solution, you must remove your manually inserted values in the Property dialog for the ComboBox1's RowSource.

I have attached the file that I worked on. I seldom post code that I have not tested.

**Kenneth Hobson** · Nov 3rd 2015

Re: Dynamisk Search List DropDown in a Userform, ComboBox

I have used Filter() for that type of thing in the past.
e.g.

Code

Private Sub ComboBox1_Change()
  Sheets(1).Select
  Application.Goto "R2C3"
    
    
  Dim r As Range, v() As Variant, s() As String
  'Set r = Range("InSheetDropDownList")
  Set r = Worksheets("Ark1").Range("C2", _
    Worksheets("Ark1").Range("C" & Rows.Count).End(xlUp))
  v() = WorksheetFunction.Transpose(r)


  On Error Resume Next
  
  With ComboBox1
    If .Value = "" Then .List = v()
    s() = Filter(WorksheetFunction.Transpose(.List), .Text, True, vbTextCompare)
    If UBound(s) = -1 Then
      .Text = ""
      Else
      .List = WorksheetFunction.Transpose(s)
    End If
  End With
    
    
  Call Module1.Update
End Sub

Display More

Dynamisk Search List DropDown in a Userform, ComboBox

Participate now!

Share