Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Fluid combobox in userform

  1. #1
    New Member
    Join Date
    Aug 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Fluid combobox in userform

    Hi there,

    This may take a while as what I am trying to acheive I believe is quite complicated. So I have a table on Sheet2 of my excel workbook, which is poulated by a userfrom, from range BO:BT. In column BO there will be a unique identifier that will appear usually more than once in the column.

    I would then like to use the userform, based upon the value in a userform textbox matching with the unique identifier in column BO, to show in a combobox all the corresponding values linked to the unique identifier from the table in column BQ.
    The values shown in the combobox would then obviously change if the unique identifier changes in the userform, based upon the table in sheet2.

    The difficulty is these are not set values and will be fluid, therefore the will not be predictable prior to the user entering the information. The table will be continually updating. Any help will be much appreciated.

    Thanks in advance,
    Andrew

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    14,219
    Post Thanks / Like
    Mentioned
    259 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Fluid combobox in userform

    How about
    Code:
    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
       Dim Cl As Range
       
       With CreateObject("scripting.dictionary")
          For Each Cl In Range("BO2", Range("BO" & Rows.Count).End(xlUp))
             If LCase(Cl.Value) = LCase(Me.TextBox1.Value) Then .Item(Cl.Offset(, 2).Value) = Empty
          Next Cl
          Me.ComboBox1.List = .keys
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  3. #3
    New Member
    Join Date
    Aug 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fluid combobox in userform

    Thanks so much, I will give this a go and let you know if it works!

  4. #4
    New Member
    Join Date
    Aug 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fluid combobox in userform

    Hey,

    I tried the code but it doesn't appear to be returning the related values from column BQ in the combobox. The combobox is staying empty. Here is my code:

    Private Sub TextBox_UniqueIdentifier2_Change()
    Dim Cl As Range

    With CreateObject("Scripting.Dictionary")
    For Each Cl In Range("BO4", Range("BO" & Rows.Count).End(xlUp))
    If LCase(Cl.Value) = LCase(Me.TextBox_UniqueIdentifier2.Value) Then .Item(Cl.Offset(, 2).Value) = Empty
    Next Cl

    Me.ComboBox_SKIP.List = .keys
    End With

    With Me
    .Operation_Master = Application.WorksheetFunction.VLookup((Me.TextBox_UniqueIdentifier2), Sheet1.Range("VLOOKUPGOOD"), 11, 0)
    .Operation_Follow = Application.WorksheetFunction.VLookup((Me.TextBox_UniqueIdentifier2), Sheet1.Range("VLOOKUPGOOD"), 12, 0)
    End With
    End Sub

    Is it because I am using the change function rather than the exit as in your example?

    Thanks,
    Andrew

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    14,219
    Post Thanks / Like
    Mentioned
    259 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Fluid combobox in userform

    Is the sheet with cols BO & BQ active when you run the userform?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  6. #6
    New Member
    Join Date
    Aug 2018
    Posts
    36
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fluid combobox in userform

    Hey, my mistake I was using the wrong identifier. Now I have changed to the right one it works perfectly. Thanks so much !

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    14,219
    Post Thanks / Like
    Mentioned
    259 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Fluid combobox in userform

    Glad it's working & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •