View original (French)

Filtering data with a ComboBox

Zanlepan -  
 Zanlepan -
Bonjour,

I created a table where I included a comboBox in each column header to filter the row content. I am able to fill the combo boxes, but I cannot apply the filter. That is, when I select a value from the comboBox, it appears in it, but the rest of the table does not change. I would also like to know how to display a default value in the combo box (I want it to say "Select a port"). Here is my code: 

Sub CB_loadPort_Change() With Sheets("calculs") premierPort = .Range("AA1").Address dernierPort = .Range(premierPort).End(xlDown).Address ports = .Range(premierPort & ":" & dernierPort) End With With ActiveSheet.CB_loadPort For Each portName In ports .AddItem portName Next portName .Style = fmStyleDropDownList .AutoSize = False End With End Sub


Thank you!

1 answer

  1.
    pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
     
    Bonjour,

    In your message, you give us a code.
    Can you tell me what the following lines do: 

    With ActiveSheet.CB_loadPort For Each portName In ports .AddItem portName Next portName .Style = fmStyleDropDownList .AutoSize = False End With


    Also, describe this: 
    Sub CB_loadPort_Change()


    EDIT: additional information: where and how are your variables declared?

    Before, I could never finish my sentences... but now I
    0
    1. Zanlepan
       
      Here is the declaration of my variables: 
      Option Explicit Option Base 1 Public vessels, vesselName, actualYear, ws, newpath, ports, portName As Variant Public premierNavire, dernierNavire, newFileName, annee, premierPort, dernierPort As String


      The first part of the code you wrote is used to take the list located on the vessels page and fill my comboBox with it (the latter is located on another sheet)

      My comboBox is named CB_loadPort, the subroutine CB_loadPort_change() is called when clicking on a commandButton on the Template sheet. This command button copies the template sheet, renames it, and fills the comboBox which is initially empty on this sheet.
      0
      1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772 > Zanlepan
         
        Hello,

        The issue here is that the CB_loadPort_change() procedure is an event procedure that triggers every time a value changes in the CB_loadPort combobox. Basically, when you select a value or even type a single character into that Combo, the code triggers and... re-fills your Combobox.

        1- The code for the CommandButton must be in the CommandButtonXX_Click procedure (where XX is the button number).

        2- In VBA, we type every variable unless it is a Variant. Your variables: 
        Public vessels, vesselName, actualYear, ws, newpath, ports, portName 'these are Variants Public premierNavire As String, dernierNavire As String, newFileName As String, annee As String, premierPort As String, dernierPort As String

        3- The code for your combobox, which is supposed to filter the sheet, should not be in the CB_loadPort_Change() procedure but in the CB_loadPort_Click(). This is indeed the event that should be used to ensure that every time a value is selected in the combo, the macro triggers.

        4- You have one Combobox per column. The goal here will be to have just one procedure for all your comboboxes. This will give you code like: 

        Sub Filtre(ByRef Col As Integer, ByRef Valeur_Combo As String) Dim DLig As Long, PremLig As Long, i As Long PremLig = 2 DLig = Columns(Col).Find("*", , , , xlByColumns, xlPrevious).Row For i = PremLig To DLig If Cells(i, Col) <> Valeur_Combo Then Rows(i).EntireRow.Hidden = True Next i End Sub


        Code that you will call with: 
        Sub CB_loadPort_Click() If CB_loadPort <> "" And CB_loadPort <> "Select a port" Then Call Filtre(3, CB_loadPort.Value) 'or 3 is the "hardcoded" number of the column where your combo is End If End Sub


        5- Since you are filtering your sheet, you also need a Sub to display everything

        6- To give a default value to your combobox:
        CB_LoadPort.Text = "Select a port"
        0
      2. Zanlepan > pijaku Posted messages 13513 Registration date   Status Moderator Last intervention  
         
        WOAH! Thank you, what a beautiful clear and detailed response, I didn't expect that!!!
        I will take the time to analyze all this, but really, thank you a thousand times!
        0