Populate multiple comboboxes (userform) from a VBA worksheet
Solved
seb3386
Posted messages
3
Status
Membre
-
seb3386 Posted messages 3 Status Membre -
seb3386 Posted messages 3 Status Membre -
Hello everyone,
I am working on creating a form in Excel using userforms (navigating from one Userform to another with "Next" and "Back" buttons).
On one of the userforms (Userform03), I would like to insert comboboxes (9 in total), which will be populated from data in a sheet of my Excel file, both by:
- removing duplicates
- and presenting the data of the combobox in alphabetical order (since the data in the Excel sheet is not sorted and should not be sorted).
To populate my Combobox1 on my Userform03, here is what I have done:
It works quite well for a single combobox. However, when I try to fill the other Comboboxes, it gets complicated.
I tried duplicating (as many times as there are comboboxes) the procedure by creating new variables (in the same sub), but transitioning from Userform02 to Userform03 does not work. I get the following runtime error when trying to switch from Userform02 to Userform03: "Run-time error '9': index is out of range."
When I click on debug, the following line (found in the code of Userform2) is highlighted in yellow and is the source of the bug (in bold and underlined below):
Could you have a solution to my problem, or an alternative way to fill multiple comboboxes in a userform?
Thank you very much for your attention.
Sébastien
I am working on creating a form in Excel using userforms (navigating from one Userform to another with "Next" and "Back" buttons).
On one of the userforms (Userform03), I would like to insert comboboxes (9 in total), which will be populated from data in a sheet of my Excel file, both by:
- removing duplicates
- and presenting the data of the combobox in alphabetical order (since the data in the Excel sheet is not sorted and should not be sorted).
To populate my Combobox1 on my Userform03, here is what I have done:
Private Sub UserForm_Initialize()
Dim sd As Object
Dim str As Range
Dim cel As Range
Dim tbl As Variant
Dim i As Integer
Dim j As Integer
Dim temp As Variant
Set sd = CreateObject("Scripting.Dictionary")
With Sheets("Entities") 'sheet where my data to fill the combobox is located
Set str = .Range(Range("C2"), Range("C65536").End(xlUp)) 'defines the data range
End With
For Each cel In str
sd(cel.Value) = "" 'fills the list without duplicates
Next cel
tbl = sd.keys 'defines the array of unique values
'alphabetical sorting
For i = 0 To UBound(tbl)
For j = 0 To UBound(tbl)
If tbl(i) < tbl(j) Then
temp = tbl(i)
tbl(i) = tbl(j)
tbl(j) = temp
End If
Next j
Next i
Me.ComboBox1.List = tbl
End Sub
It works quite well for a single combobox. However, when I try to fill the other Comboboxes, it gets complicated.
I tried duplicating (as many times as there are comboboxes) the procedure by creating new variables (in the same sub), but transitioning from Userform02 to Userform03 does not work. I get the following runtime error when trying to switch from Userform02 to Userform03: "Run-time error '9': index is out of range."
When I click on debug, the following line (found in the code of Userform2) is highlighted in yellow and is the source of the bug (in bold and underlined below):
Private Sub CommandButton1_Click()
'Go to UserForm03
Unload Me
UserForm03.Show
End Sub
Could you have a solution to my problem, or an alternative way to fill multiple comboboxes in a userform?
Thank you very much for your attention.
Sébastien
6 réponses
Hello,
It's hard to understand because it should work.
A question:
OK, I didn't read it well so yes there is some code and it's indeed the initialize procedure that causes this, so is the name of the sheet "Entities" correctly spelled?
Are the other combos filled with data from other sheets?
I think there might be an incorrect sheet name in the initialize macro.
Best regards,
It's hard to understand because it should work.
A question:
OK, I didn't read it well so yes there is some code and it's indeed the initialize procedure that causes this, so is the name of the sheet "Entities" correctly spelled?
Are the other combos filled with data from other sheets?
I think there might be an incorrect sheet name in the initialize macro.
Best regards,
Hello,
Your problem comes from the fact that your code is in:
Private Sub UserForm_Initialize()
It needs to be placed in:
Private Sub UserForm_Activate()
like this
--
@+ The Woodpecker
Your problem comes from the fact that your code is in:
Private Sub UserForm_Initialize()
It needs to be placed in:
Private Sub UserForm_Activate()
like this
Private Sub UserForm_Activate() Dim sd As Object Dim str As Range Dim cel As Range Dim tbl As Variant Dim i As Integer Dim j As Integer Dim temp As Variant Set sd = CreateObject("Scripting.Dictionary") With Sheets("Entities") 'sheet where my data that feeds my combobox is located Set str = .Range(Range("C2"), Range("C65536").End(xlUp)) 'defines the data range End With For Each cel In str sd(cel.Value) = "" 'fills the list without duplicates Next cel tbl = sd.keys 'defines the array of unique values 'alphabetical sorting For i = 0 To UBound(tbl) For j = 0 To UBound(tbl) If tbl(i) < tbl(j) Then temp = tbl(i) tbl(i) = tbl(j) tbl(j) = temp End If Next j Next i Me.ComboBox1.List = tbl With Sheets("Entities") 'sheet where my data that feeds my combobox is located Set str = .Range(Range("D2"), Range("C65536").End(xlUp)) 'defines the data range End With For Each cel In str sd(cel.Value) = "" 'fills the list without duplicates Next cel tbl = sd.keys 'defines the array of unique values 'alphabetical sorting For i = 0 To UBound(tbl) For j = 0 To UBound(tbl) If tbl(i) < tbl(j) Then temp = tbl(i) tbl(i) = tbl(j) tbl(j) = temp End If Next j Next i Me.ComboBox2.List = tbl End Sub --
@+ The Woodpecker
but the transition from Userform02 to Userform03 does not work.
Use this:
the names of the UserForms should be adapted
--
@+ The Woodpecker
Use this:
Private Sub CommandButton1_Click() lancementUSF UserForm2.Hide End Sub 'http://silkyroad.developpez.com/VBA/UserForm/ Sub lancementUSF() Dim sVariable As String sVariable = "UserForm1" VBA.UserForms.Add(sVariable).Show End Sub
the names of the UserForms should be adapted
--
@+ The Woodpecker
Hello Pilas, Hello cs_Le Pivert, Hello forum,
Thank you very much for your responses.
Thank you Pilas:
The name of the sheet "Entities" is spelled correctly, I have checked it.
The other combo boxes are supposed to be, for part of them, sourced from the same sheet, and for another part from another Excel sheet.
I have checked the spellings well, but that’s not where the issue comes from.
Thank you cs_Le Pivert:
I just replaced
I now have another error. It comes from the range definition. The highlighted line is line number 13 in cs_Le Pivert's message. I will try to find out where it comes from, but I’m not seeing it clearly...
Thank you very much for your responses.
Thank you Pilas:
The name of the sheet "Entities" is spelled correctly, I have checked it.
The other combo boxes are supposed to be, for part of them, sourced from the same sheet, and for another part from another Excel sheet.
I have checked the spellings well, but that’s not where the issue comes from.
Thank you cs_Le Pivert:
I just replaced
Private Sub UserForm_Initialize()with
Private Sub UserForm_Activate()
I now have another error. It comes from the range definition. The highlighted line is line number 13 in cs_Le Pivert's message. I will try to find out where it comes from, but I’m not seeing it clearly...