SOLVED

(Solved) I would like to order a table of 3 columns, through two comboboxes

Contributor

Good Morning
I would like to order a table of 3 columns, through two comboboxes in a form, in which the user chooses the options he wants, to order by 2 columns.
When I try to associate a variable to the column chosen by the user, I get an error "Invalid or unqualified reference".
How can I make it work? Thanks

 

Private Sub Btn_Imprimir_Click()
     Dim v_Sorting1 As String
     Dim v_Sorting2 As String

     If Form_Imprimir.ComboBox1.Value = "Tipo" Then v_Sorting1 = ".ListColumns(1)"
     If Form_Imprimir.ComboBox1.Value = "Equipamento" Then Set v_Sorting1 = .ListColumns(2)
     If Form_Imprimir.ComboBox1.Value = "Data Fabrico" Then v_Sorting1 = ".ListColumns(3)"

     If Form_Imprimir.ComboBox2.Value = "Tipo" Then v_Sorting2 = ".ListColumns(1)"
     If Form_Imprimir.ComboBox2.Value = "Equipamento" Then v_Sorting2 = ".ListColumns(2)"
     If Form_Imprimir.ComboBox2.Value = "Data Fabrico" Then Set v_Sorting2 = .ListColumns(3)

     Folha3.Sort.SortFields.Clear
     With Folha3.ListObjects(1)
          .Range.Sort Key1:=v_Sorting1, Key2:=v_Sorting2, Header:=xlYes, Order1:=xlAscending,                               Order1:=xlAscending
     End With
End Sub

4 Replies
best response confirmed by csoares (Contributor)
Solution

@csoares 

Key1 and K2 are not strings.

 

Private Sub Btn_Imprimir_Click()
    Dim v_Sorting1
    Dim v_Sorting2

    With Folha3.ListObjects(1)
        Select Case ComboBox1.Value
            Case "Tipo"
                Set v_Sorting1 = .ListColumns(1)
            Case "Equipamento"
                Set v_Sorting1 = .ListColumns(2)
            Case "Data Fabrico"
                Set v_Sorting1 = .ListColumns(3)
            Case Else
                Beep
                Exit Sub
        End Select

        Select Case ComboBox2.Value
            Case "Tipo"
                Set v_Sorting2 = .ListColumns(1)
            Case "Equipamento"
                Set v_Sorting2 = .ListColumns(2)
            Case "Data Fabrico"
                Set v_Sorting2 = .ListColumns(3)
            Case Else
                Beep
                Exit Sub
        End Select

        .Range.Sort Key1:=v_Sorting1, Key2:=v_Sorting2, Header:=xlYes
     End With
End Sub

@Hans Vogelaar 

Thank you so much for the help, because it worked perfectly and I was going through the wrong thought.
However, I tried to do the same method for Order1 and Order2 with other comboboxes, but I get an error that says "Object is mandatory". I tried to set the variable to String but it didn't work either.
I still have a bit of confusion as to whether I should define an object or a string. Thank you for your help.

@csoares 

The Key1 and Key2 arguments are objects, so you must use the keyword Set.

The Order1 and Order2 arguments are not objects but number.s Don't use the keyword Set for them:

 

v_Order1 = xlAscending

and

v_Order1 = xlDescending

Thanks for the Help. You solve my problem
www.000webhost.com