Combobox and ListIndex
Solved
wire less
Posted messages
247
Registration date
Status
Member
Last intervention
-
pijaku Posted messages 13513 Registration date Status Moderator Last intervention -
pijaku Posted messages 13513 Registration date Status Moderator Last intervention -
Hello,
I have two questions:
I am currently initializing a "user form" and I have different comboboxes and listboxes.
1) First of all, I want to implement my combobox list
To create a list without "duplicates," I retrieved some code circulating on forums:
In this piece of code, I would like to understand what the line
means, and especially:
???
2) Next, I have a problem when executing this snippet of code.
I included this small piece of code in a loop to initialize my combobox, and periodically, it activates
Is there a way to disable any "click" on my ComboBox1 while waiting for my first script to finish??
Thank you very much
Configuration: Windows 2003 / Chrome 43.0.2357.130
I have two questions:
I am currently initializing a "user form" and I have different comboboxes and listboxes.
1) First of all, I want to implement my combobox list
To create a list without "duplicates," I retrieved some code circulating on forums:
ComboBox1 = Variable_A_Ajouté_N°1
If ComboBox1.ListIndex = -1 Then ComboBox1.AddItem Variable_A_Ajouté_N°1
In this piece of code, I would like to understand what the line
ComboBox1 = Variable_A_Ajouté_N°1
means, and especially:
If ComboBox1.ListIndex = -1
???
2) Next, I have a problem when executing this snippet of code.
I included this small piece of code in a loop to initialize my combobox, and periodically, it activates
Private Sub ComboBox1_Click()
Is there a way to disable any "click" on my ComboBox1 while waiting for my first script to finish??
Thank you very much
Configuration: Windows 2003 / Chrome 43.0.2357.130
6 answers
Hello Wireless, hello forum,
the fastest way to populate a ListBox/ComboBox without duplicates is by using a dictionary. Generally, this population is done through the initialization of the UserForm that contains it.
Take a look at the commented example below:
Using a variable "Array of cells" is much faster than actually looping through the cells of an array. This also greatly increases the execution speed of the code...
http://www.cjoint.com/c/EGhrkgxVzeR
--
See you,
ThauTheme
the fastest way to populate a ListBox/ComboBox without duplicates is by using a dictionary. Generally, this population is done through the initialization of the UserForm that contains it.
Take a look at the commented example below:
Private O As Worksheet 'declares the variable O (Sheet) Private TC As Variant 'declares the variable TC (Array of Cells) Private Sub UserForm_Initialize() 'on the initialization of the UserForm Dim I As Long 'declares the variable I (Increment) Dim D As Object 'declares the variable D (Dictionary) Set O = Sheets("Feuil1") 'defines the sheet O TC = O.Range("A1").CurrentRegion 'defines the array of cells TC Set D = CreateObject("Scripting.Dictionary") 'defines the dictionary D For I = 2 To UBound(TC, 1) 'loops through all the rows I of the array of cells TC (starting from the second one) D(TC(I, 1)) = "" 'populates the dictionary D Next I 'next line of the loop Me.ComboBox1.List = D.keys 'populates ComboBox1 with the list of items from the dictionary D without duplicates End Sub Using a variable "Array of cells" is much faster than actually looping through the cells of an array. This also greatly increases the execution speed of the code...
http://www.cjoint.com/c/EGhrkgxVzeR
--
See you,
ThauTheme
Re,
No need to filter. Check the example below:
http://www.cjoint.com/c/EGitkzkIIRR
--
See you later,
ThauTheme
No need to filter. Check the example below:
http://www.cjoint.com/c/EGitkzkIIRR
--
See you later,
ThauTheme
I have one last real question ThauTheme.
I have a big problem with my tests:
First test:
If TC(I, 1) = Me.ComboBox1.Value ... ' Ok, everything works fine. I have 'Toto' = 'Toto' ...
Second Test:
If TC(I, 2) = Me.ComboBox2.Value ... ' This time I have 1 =/= "1" ... and now I'm really stuck!
I tried to define my variable TC As String to force "1" = "1" but then VBA no longer recognizes TC as an array!!
Do you have any idea???
Thank you.
Private TC As Variant 'declares the variable TC (Table of Cells) For I = 2 To UBound(TC, 1) 'loop through all the row I of the cell array TC (starting from the second) If TC(I, 1) = Me.ComboBox1.Value And TC(I, 2) = Me.ComboBox2.Value Then D(TC(I, 3)) = "" 'fills the dictionary D Next I 'next line of the loop
I have a big problem with my tests:
First test:
If TC(I, 1) = Me.ComboBox1.Value ... ' Ok, everything works fine. I have 'Toto' = 'Toto' ...
Second Test:
If TC(I, 2) = Me.ComboBox2.Value ... ' This time I have 1 =/= "1" ... and now I'm really stuck!
I tried to define my variable TC As String to force "1" = "1" but then VBA no longer recognizes TC as an array!!
Do you have any idea???
Thank you.
:-) I know ... I know ... But I was still waiting a bit to see if there wasn't a hidden problem somewhere ... :-p
Thanks again.
See you later
Thanks again.
See you later
For i = 1 to 1000 Call Msgbox(" THANK YOU ") Next i
Hey hey !! :-/
I have another question...
I'll try to be short and clear...
I would like to use my comboboxes with the Autofilter function in Excel...
So
I have a Combobox1 associated with the dictionary of column 1
a Combobox2 associated with the dictionary of column 2
a Combobox3 associated with the dictionary of column 3
So, after clicking on my Combobox1, I do a
I reset my combobox1&2&3, and I would like my combobox2&3 to account for the filtered items...
How do I do that :-/ ??
Thanks :-|
I have another question...
I'll try to be short and clear...
I would like to use my comboboxes with the Autofilter function in Excel...
So
I have a Combobox1 associated with the dictionary of column 1
a Combobox2 associated with the dictionary of column 2
a Combobox3 associated with the dictionary of column 3
So, after clicking on my Combobox1, I do a
Sheets("Ma_feuille").Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=ComboBox1.Value I reset my combobox1&2&3, and I would like my combobox2&3 to account for the filtered items...
How do I do that :-/ ??
Thanks :-|
Hello you two,
I will try to answer the wireless questions.
1- What does this code do:
A ComboBox is a "mix" between a textbox and a listbox. It is a list in which you can enter whatever you want.
So, the line:
What happens when you manually enter a value into a ComboBox?
Simply, if the entered value is already part of the ComboBox.List, it gets selected. This results in changing the ListIndex property to the index value of the entered value.
If it is not part of it, nothing happens; you just entered it.
Is that all? Well, actually no. If the entered value is not part of the ComboBox.List, the ListIndex property remains at -1 (the first index of non-empty ComboBoxes being 0).
So, the code above:
If the ListIndex is still -1, it means the entered value is not yet part of the ComboBox.List, therefore... You can add it with .AddItem...
2- Is there a way to disable events of a UserForm?
Yes, using a public boolean variable.
A little test you will recognize ;-)
In step mode, you will see the entry and exit of the Private Sub _Change()
3- What is the difference between "_Click()" and "_Change()"?
Click is the event that occurs when you click on the ComboBox.
Change is the event that occurs when you input into your ComboBox.
During a click, the Change event occurs if the value of the combobox changes...
During a change via manual input in a combobox, Click does not trigger.
This raises the real problem, the real question: should I allow value input into my ComboBox?
Often the answer is no, but the developer has not considered this fact: the user can be foolish.
If the user enters any nonsense into the ComboBox, it can crash the tool.
To avoid this, simply prevent input of values not included in the ComboBox.List.
How to do this?
Simply remember to set the .Style property of the ComboBox to: 2 (fmStyleDropDownList)
An example based on the code above:
PS: I will repeat it here, but it is important, there is no need for variables as bulky as the dictionary object to fill a ComboBox.
A++
🎼 Best regards,
Franck 🎶
I will try to answer the wireless questions.
1- What does this code do:
ComboBox1 = Variable_A_Ajouté_N°1 If ComboBox1.ListIndex = -1 Then ComboBox1.AddItem Variable_A_Ajouté_N°1
A ComboBox is a "mix" between a textbox and a listbox. It is a list in which you can enter whatever you want.
So, the line:
ComboBox1 = Variable_A_Ajouté_N°1(implied:
ComboBox1.Value = Variable_A_Ajouté_N°1) simply enters the content of the variable Variable_A_Ajouté_N°1 into the combobox.
What happens when you manually enter a value into a ComboBox?
Simply, if the entered value is already part of the ComboBox.List, it gets selected. This results in changing the ListIndex property to the index value of the entered value.
If it is not part of it, nothing happens; you just entered it.
Is that all? Well, actually no. If the entered value is not part of the ComboBox.List, the ListIndex property remains at -1 (the first index of non-empty ComboBoxes being 0).
So, the code above:
- enters your variable into the combobox
- tests the ListIndex of your combobox.
If the ListIndex is still -1, it means the entered value is not yet part of the ComboBox.List, therefore... You can add it with .AddItem...
2- Is there a way to disable events of a UserForm?
Yes, using a public boolean variable.
A little test you will recognize ;-)
In step mode, you will see the entry and exit of the Private Sub _Change()
Public Evenements_Actifs As Boolean Private Sub ComboBox1_Change() If Me.Evenements_Actifs = False Then Exit Sub MsgBox "Change" End Sub Private Sub ComboBox1_Click() MsgBox "Click" End Sub Private Sub UserForm_Initialize() Dim TABLEAU As Variant, I As Long TABLEAU = Sheets("Sheet1").Range("A2:A6") Call Tri(TABLEAU, LBound(TABLEAU), UBound(TABLEAU)) Me.Evenements_Actifs = False For I = 1 To UBound(TABLEAU, 1) ComboBox1.Value = TABLEAU(I, 1) 'avoid duplicates If ComboBox1.ListIndex = -1 Then ComboBox1.AddItem TABLEAU(I, 1) Next I Me.Evenements_Actifs = True End Sub Sub Tri(a, gauc, droi) ' Quick sort Dim ref, g, d, tmp 'As filling of the initial array by the Range object 'you need to add the dimension ( , 1) otherwise an error 9 ref = a((gauc + droi) \ 2, 1) g = gauc: d = droi Do Do While a(g, 1) < ref: g = g + 1: Loop Do While ref < a(d, 1): d = d - 1: Loop If g <= d Then tmp = a(g, 1): a(g, 1) = a(d, 1): a(d, 1) = tmp g = g + 1: d = d - 1 End If Loop While g <= d If g < droi Then Call Tri(a, g, droi) If gauc < d Then Call Tri(a, gauc, d) End Sub 3- What is the difference between "_Click()" and "_Change()"?
Click is the event that occurs when you click on the ComboBox.
Change is the event that occurs when you input into your ComboBox.
During a click, the Change event occurs if the value of the combobox changes...
During a change via manual input in a combobox, Click does not trigger.
This raises the real problem, the real question: should I allow value input into my ComboBox?
Often the answer is no, but the developer has not considered this fact: the user can be foolish.
If the user enters any nonsense into the ComboBox, it can crash the tool.
To avoid this, simply prevent input of values not included in the ComboBox.List.
How to do this?
Simply remember to set the .Style property of the ComboBox to: 2 (fmStyleDropDownList)
An example based on the code above:
Public Evenements_Actifs As Boolean Private Sub ComboBox1_Change() If Me.Evenements_Actifs = False Then Exit Sub MsgBox "Change" End Sub Private Sub ComboBox1_Click() MsgBox "Click" End Sub Private Sub UserForm_Initialize() Dim TABLEAU As Variant, I As Long TABLEAU = Sheets("Sheet1").Range("A2:A6") Call Tri(TABLEAU, LBound(TABLEAU), UBound(TABLEAU)) Me.ComboBox1.Style = fmStyleDropDownCombo ' ====> Otherwise problem: input impossible Me.Evenements_Actifs = False For I = 1 To UBound(TABLEAU, 1) ComboBox1.Value = TABLEAU(I, 1) ' ===> Here we have an input!!! 'to avoid duplicates If ComboBox1.ListIndex = -1 Then ComboBox1.AddItem TABLEAU(I, 1) Next I Me.Evenements_Actifs = True Me.ComboBox1.Style = fmStyleDropDownList ' ====> Input of any nonsense impossible End Sub Sub Tri(a, gauc, droi) ' Quick sort Dim ref, g, d, tmp 'As filling of the initial array by the Range object 'you need to add the dimension ( , 1) otherwise an error 9 ref = a((gauc + droi) \ 2, 1) g = gauc: d = droi Do Do While a(g, 1) < ref: g = g + 1: Loop Do While ref < a(d, 1): d = d - 1: Loop If g <= d Then tmp = a(g, 1): a(g, 1) = a(d, 1): a(d, 1) = tmp g = g + 1: d = d - 1 End If Loop While g <= d If g < droi Then Call Tri(a, g, droi) If gauc < d Then Call Tri(a, gauc, d) End Sub PS: I will repeat it here, but it is important, there is no need for variables as bulky as the dictionary object to fill a ComboBox.
A++
🎼 Best regards,
Franck 🎶
ThauTheme :-/ !!!
Your code works great !! And on top of that, no more issues with the triggering of "Private Sub ComboBox1_Click()" unexpectedly :-D and that's great too !!
Thank you :-)