Vba excel filter / listbox ...
ludivine
-
amine69500 Posted messages 471 Status Member -
amine69500 Posted messages 471 Status Member -
Hello,
As a beginner in VBA Excel, I am encountering quite a few issues despite many searches on the internet.
I would like to display the content WITHOUT DUPLICATES from a column in a dropdown list on a form, knowing that the values to display only start from row 3 (I succeeded but the duplicates are still there!)
I would also like to create a code to remove the filters from all columns except column 1 of my sheet. (without having to write one line of code for each column)
Can someone help me? It’s tough to fumble around... lol
Thanks in advance
Ludivine
As a beginner in VBA Excel, I am encountering quite a few issues despite many searches on the internet.
I would like to display the content WITHOUT DUPLICATES from a column in a dropdown list on a form, knowing that the values to display only start from row 3 (I succeeded but the duplicates are still there!)
I would also like to create a code to remove the filters from all columns except column 1 of my sheet. (without having to write one line of code for each column)
Can someone help me? It’s tough to fumble around... lol
Thanks in advance
Ludivine
Configuration: windows/excel97 and + /vb 6
15 answers
Try this macro, to be run when you open your workbook. It creates a sorted list without duplicates in a special sheet that I call Param (no need to create the sheet, it does it on its own if it doesn't exist), and gives it a name. Everything is recalculated every time you open the workbook. The name of the range is PlageListBox (you can change anything you want...).
And in the properties of your ListBox, you can set the name of this range in RowSource.
And in the properties of your ListBox, you can set the name of this range in RowSource.
Sub PrepaListe() Dim Lig_Deb As Long Dim DerLig As Long Dim Col As String Dim Feuille As String Dim CelluleCourante As Range Dim CelluleSuivante As Range Dim FF As Worksheet ' PARAMETRES A ADAPTER '---------------------------------------------------------------------------- Lig_Deb = 3 ' n° de la première ligne de données filtrées Col = "A" ' colonne de données filtrées Feuille = "Feuil1" ' nom de la feuille de données '---------------------------------------------------------------------------- Application.ScreenUpdating = False On Error Resume Next Worksheets("Param").Activate If Err.Number = 9 Then Worksheets.Add ActiveSheet.Name = "Param" End If On Error GoTo 0 Sheets("Param").Cells.ClearContents Set FF = Sheets(Feuille) DerLig = FF.Cells(65536, Col).End(xlUp).Row FF.Range(Col & Lig_Deb & ":" & Col & DerLig).Copy Cells(1, 1).Select ActiveSheet.Paste Selection.Sort Key1:=Range("A1"), _ Order1:=xlAscending, _ Header:=xlNo Application.CutCopyMode = False Set CelluleCourante = Range("A1") Do While Not IsEmpty(CelluleCourante) Set CelluleSuivante = CelluleCourante.Offset(1, 0) If CelluleSuivante.Value = CelluleCourante.Value Then CelluleCourante.EntireRow.Delete End If Set CelluleCourante = CelluleSuivante Loop On Error Resume Next ActiveWorkbook.Names("PlageListBox").Delete On Error GoTo 0 DerLig = Range("A65536").End(xlUp).Row ActiveWorkbook.Names.Add Name:="PlageListBox", _ RefersToR1C1:="=Param!R1C1:R" & DerLig & "C1" Application.ScreenUpdating = True End Sub
Hello,
I created a dropdown list using Excel "data/validation" in column A, and in the same way, a second dropdown list in column B. However, my boss is asking that when a value is selected from column A, column B should only display the data corresponding to that value in column B.
Example:
Dropdown list in column A: "a;b;c;d;e"
Dropdown list in column B: "aa;ab;ac;ad;af;ba;bb;bc;bd..."
The request:
When selecting in column A: the value "a",
the dropdown list in column B should only display the values that start with "a": {aa;ab;ac;ad;af}
and not the entire list since I have 12,000 values and the user really gets lost.
Thank you in advance for your cooperation.
I created a dropdown list using Excel "data/validation" in column A, and in the same way, a second dropdown list in column B. However, my boss is asking that when a value is selected from column A, column B should only display the data corresponding to that value in column B.
Example:
Dropdown list in column A: "a;b;c;d;e"
Dropdown list in column B: "aa;ab;ac;ad;af;ba;bb;bc;bd..."
The request:
When selecting in column A: the value "a",
the dropdown list in column B should only display the values that start with "a": {aa;ab;ac;ad;af}
and not the entire list since I have 12,000 values and the user really gets lost.
Thank you in advance for your cooperation.
Re, Ludivine,
OK. Try this, to add in the Initialize macro of your UserForm. Adjust the parameters:
- Lig: row number of the first data
- Col: column of the data
- ListBox1: possibly replace with the name of your ListBox.
OK. Try this, to add in the Initialize macro of your UserForm. Adjust the parameters:
- Lig: row number of the first data
- Col: column of the data
- ListBox1: possibly replace with the name of your ListBox.
Private Sub UserForm_Initialize() Dim Lig As Long Dim Col As String Dim ValeurCourante As String Dim ValeurPrécédente As String Lig = 3 Col = "A" ValeurPrécédente = "" ValeurCourante = Cells(Lig, Col).Value Do While ValeurCourante <> "" If ValeurCourante <> ValeurPrécédente Then ListBox1.AddItem ValeurCourante End If Lig = Lig + 1 ValeurPrécédente = ValeurCourante ValeurCourante = Cells(Lig, Col).Value Loop End SubFor the filters, what I gave you does not work? It removes all filters, and reinstalls the one for column "A".
Hello Ludivine,
To remove duplicates in column "A" starting from row 3:
EntireRow deletes the entire row. Remove it to only delete the cell.
For filters:
To remove duplicates in column "A" starting from row 3:
Dim CurrentCell As Range Dim NextCell As Range Set CurrentCell = Range("A3") Do While Not IsEmpty(CurrentCell) Set NextCell = CurrentCell.Offset(1, 0) If NextCell.Value = CurrentCell.Value Then CurrentCell.EntireRow.Delete End If Set CurrentCell = NextCell Loop If you don't want to delete your duplicates, but just not display them, you can take inspiration from this, or work on another temporary column in which you have copied the data. EntireRow deletes the entire row. Remove it to only delete the cell.
For filters:
ActiveSheet.AutoFilterMode = False Columns("A:A").AutoFilter
Hello Armojax,
Thank you for your reply, however I may not have expressed myself well:
actually, I have a sheet with a database
the goal is to access the data through forms (to simplify the search)
the listbox in the form is to filter column A, so it displays the list of values from column A without duplicates (for visualization...), they select and it returns the value in the auto filter
in the other procedure (filters), I did:
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4
...
so as not to touch the filter of my column A which must remain filtered, while keeping the ability to access the filters of the sheet ...
it works fine, of course, but there must be a shorter way lol
??
Ludivine
Thank you for your reply, however I may not have expressed myself well:
actually, I have a sheet with a database
the goal is to access the data through forms (to simplify the search)
the listbox in the form is to filter column A, so it displays the list of values from column A without duplicates (for visualization...), they select and it returns the value in the auto filter
in the other procedure (filters), I did:
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4
...
so as not to touch the filter of my column A which must remain filtered, while keeping the ability to access the filters of the sheet ...
it works fine, of course, but there must be a shorter way lol
??
Ludivine
Hello,
It's not working, sniff... tough for a Friday!
I must have missed something for sure, but I don't understand anything in the first part of your code, so if I need to adapt it, it's not looking good.
Anyway, for the listbox, originally, I did have my data list... I just gave the name of the...
It's not working, sniff... tough for a Friday!
I must have missed something for sure, but I don't understand anything in the first part of your code, so if I need to adapt it, it's not looking good.
Anyway, for the listbox, originally, I did have my data list... I just gave the name of the...
No sniffing, Ludivine... !
Here’s an example:
https://www.cjoint.com/?izkDwEfevP
When the workbook opens, the Listbox is displayed automatically.
Check the code of the UserForm...
Here’s an example:
https://www.cjoint.com/?izkDwEfevP
When the workbook opens, the Listbox is displayed automatically.
Check the code of the UserForm...
ok, it can't work in my case, as the duplicates don't necessarily follow each other, unless I sort it on opening...
for the filters, (I couldn't finish everything earlier), I noted your code for later, but let me explain:
users select a value from my listbox (the one that's causing the issue), the value is sent to filter column A, I don't want them to have control over this filter, but over the others they can...
so if I reset the filter, the value won't be there anymore, unless it can be reinserted...
Don't forget that I'm a beginner, but truly a real one!
for the filters, (I couldn't finish everything earlier), I noted your code for later, but let me explain:
users select a value from my listbox (the one that's causing the issue), the value is sent to filter column A, I don't want them to have control over this filter, but over the others they can...
so if I reset the filter, the value won't be there anymore, unless it can be reinserted...
Don't forget that I'm a beginner, but truly a real one!
Sorry Ludivine, but my FreeBox was out for 3 hours. Well, 3 hours in 2 years isn't a disaster, but still...
So I'm putting a new binder here:
https://www.cjoint.com/?izrcMmdFXA
I have a better idea of what you want to do.
The form doesn't launch at startup. You can do <CTRL+SHIFT+M>.
You can set up the Initialize macro according to your own needs. I use column "Z" to copy data from column "A" and sort it to feed the ListBox.
When we click on the list, we filter the corresponding data. In the command, I set the VisibleDropDown parameter to False, which hides the little "down arrow" of the filter. You can set it to True (or remove it, that's the default value) if you like.
To be continued, no doubt...
So I'm putting a new binder here:
https://www.cjoint.com/?izrcMmdFXA
I have a better idea of what you want to do.
The form doesn't launch at startup. You can do <CTRL+SHIFT+M>.
You can set up the Initialize macro according to your own needs. I use column "Z" to copy data from column "A" and sort it to feed the ListBox.
When we click on the list, we filter the corresponding data. In the command, I set the VisibleDropDown parameter to False, which hides the little "down arrow" of the filter. You can set it to True (or remove it, that's the default value) if you like.
To be continued, no doubt...
Hello, I saw your posts about a problem similar to mine,
I created a program that sums product outputs; I have several types of products.
For each product output, I fill in text boxes (date, quantity, customer name, invoice date, delivery note number, destination, vehicle type, registration), and once this information is entered, it is automatically saved to an Excel sheet.
My problem is that I want to create another userform2 triggered by a button in userform1,
on my userform2 I want to have 3 listboxes; listbox1 displays the headers of the sheet where the data is recorded, listbox2 displays the filtered data of each column (for example, if I select customer name in listbox1, listbox2 should show all customers without duplicates, and if I select a customer in listbox2, all the transactions they made should be displayed in listbox3 in table form with all the details; thank you in advance.
I created a program that sums product outputs; I have several types of products.
For each product output, I fill in text boxes (date, quantity, customer name, invoice date, delivery note number, destination, vehicle type, registration), and once this information is entered, it is automatically saved to an Excel sheet.
My problem is that I want to create another userform2 triggered by a button in userform1,
on my userform2 I want to have 3 listboxes; listbox1 displays the headers of the sheet where the data is recorded, listbox2 displays the filtered data of each column (for example, if I select customer name in listbox1, listbox2 should show all customers without duplicates, and if I select a customer in listbox2, all the transactions they made should be displayed in listbox3 in table form with all the details; thank you in advance.
Hi
It seemed to work at one point... but now I don't understand anything
I inserted a new form, based on the answer, the form with the list box in question should display, but I'm getting a runtime error '13', I feel like it's not liking the initialization....
Are you sure there's no simpler way to complete the RowSource property of the list box (without initialization) with an additional parameter to display only unique values (a parameter that I don't know of, of course...)
??
It seemed to work at one point... but now I don't understand anything
I inserted a new form, based on the answer, the form with the list box in question should display, but I'm getting a runtime error '13', I feel like it's not liking the initialization....
Are you sure there's no simpler way to complete the RowSource property of the list box (without initialization) with an additional parameter to display only unique values (a parameter that I don't know of, of course...)
??
A priori, the VBA error "13" is Type mismatch. Perhaps text is being used as numeric, for example, or something like that...?
If your data is stable while you are using your macros, you can indeed prepare the ListBox list when opening the workbook, instead of at the Initialize of the form. In this case, you need to sort your data and create a range where duplicates are eliminated. Then you can indeed use the RowSource property to refer to this range.
If your data is stable while you are using your macros, you can indeed prepare the ListBox list when opening the workbook, instead of at the Initialize of the form. In this case, you need to sort your data and create a range where duplicates are eliminated. Then you can indeed use the RowSource property to refer to this range.
At that moment, I think I'm going to create a data range with unique values, which will serve as a source for my data column
and populate my listbox using the name of the range
it seems easier to me (unless there's a new entry for the one making changes to the database, though...), what do you think?
and populate my listbox using the name of the range
it seems easier to me (unless there's a new entry for the one making changes to the database, though...), what do you think?
Hello Armojax!
Thanks for your reply, but I haven't tested it.
Actually, I did what I described in my last message.
I created my comprehensive list (unique values) in a hidden sheet / Data validation on the data column (multiple values)
which ultimately allows me to control the input (the values are always the same (just a little button to add a value if necessary with a view of the hidden sheet...), it's safer for future events...
Plus, my listbox on the form is linked to the named range of my hidden sheet, so everything is good and without duplicates!
I just hope that using data validation doesn't risk crashing everything... (it already happened to me on another file a while ago...snif)
Thanks again, under different circumstances, I won't hesitate to use your code
Have a nice day
Ludivine
Thanks for your reply, but I haven't tested it.
Actually, I did what I described in my last message.
I created my comprehensive list (unique values) in a hidden sheet / Data validation on the data column (multiple values)
which ultimately allows me to control the input (the values are always the same (just a little button to add a value if necessary with a view of the hidden sheet...), it's safer for future events...
Plus, my listbox on the form is linked to the named range of my hidden sheet, so everything is good and without duplicates!
I just hope that using data validation doesn't risk crashing everything... (it already happened to me on another file a while ago...snif)
Thanks again, under different circumstances, I won't hesitate to use your code
Have a nice day
Ludivine
Oh yes, just one last little question!
How can I display an arrow to expand my listbox like a dropdown list? (Am I being clear?...)
For now, I have two small up and down arrows to the right of my listbox, and the values remain displayed only inside. But I would like to have just a down arrow, and when clicked, the entire list appears...
How can I display an arrow to expand my listbox like a dropdown list? (Am I being clear?...)
For now, I have two small up and down arrows to the right of my listbox, and the values remain displayed only inside. But I would like to have just a down arrow, and when clicked, the entire list appears...
Instead of a ListBox, you can use a ComboBox. Many properties are identical, and it shouldn't be complicated to transpose. You'll have a dropdown list, and you can determine the number of visible items at once with the ListRows property.
And to respond to your item No. 13 above: the macro I provided does roughly what you've set up: it automatically recalculates, in a separate sheet, a range that you can pass to RowSource. You can run it every time the workbook opens if it changes all the time, or only when it's the right time...
And to respond to your item No. 13 above: the macro I provided does roughly what you've set up: it automatically recalculates, in a separate sheet, a range that you can pass to RowSource. You can run it every time the workbook opens if it changes all the time, or only when it's the right time...