Aug 04 2021 02:06 AM - edited Aug 07 2021 11:34 PM
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
Aug 04 2021 08:56 AM
SolutionKey1 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
Aug 05 2021 02:14 AM
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.
Aug 05 2021 02:33 AM
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
Aug 07 2021 11:33 PM
Aug 04 2021 08:56 AM
SolutionKey1 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