Fluid combobox in userform

AndrewGKenneth

Board Regular
Joined
Aug 6, 2018
Messages
59
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
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
 
Upvote 0
Is the sheet with cols BO & BQ active when you run the userform?
 
Upvote 0
Hey, my mistake I was using the wrong identifier. Now I have changed to the right one it works perfectly. Thanks so much !
 
Upvote 0
Glad it's working & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top