custom sort button vba

[SarahMS1]

i need two buttons to sort my data because some days i need my data sorted in way 1 and sometimes in way 2 so i need 2 different buttons for it


way 1: its a custom sort on column M and the custom list is ( 24 H, 48 H, 3 days, 4 days, 5 days, 1 week, 2 weeks, 1 Month, 2 Months, Monthly, Yearly)
the first rows should be with 24 H and the last row ending with the Yearly

way 2: my custom sort is that the first level sort that red rows go to top and second level is green rows go to bottom and the 3rd level is sort date (column N) from oldest to newest

 

[Peter_SSs]

OK, try this version for the second one. You will need to change the particular RGB colours of your red/green cells to what you stated above as I just kept my values for testing with my colours.

Sub SortColourAndDateCustom_Click()
  Dim tbl As ListObject
  Dim rDueDateCol As Range, rUpdateCol As Range

  Set tbl = ActiveSheet.ListObjects("table3")
  Set rUpdateCol = Range("table3[Update]")
  Set rDueDateCol = Range("table3[Due Date]")
  With tbl.Sort
    With .SortFields
      .Clear
      .Add(rUpdateCol, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 0, 0)
      .Add(rUpdateCol, xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(0, 176, 80)
      .Add rDueDateCol, xlSortOnValues, xlAscending
    End With
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .Apply
  End With
End Sub

additionally i thought i can hide the button with the column but it seems like that doesnt work can u tell me what to do im fairly new to excel

Can you clarify just what you are trying to achieve?

Can you confirm that it is an ActiveX Control button, not a Form Control button?

 

[SarahMS1]

OK, try this version for the second one. You will need to change the particular RGB colours of your red/green cells to what you stated above as I just kept my values for testing with my colours.

Sub SortColourAndDateCustom_Click()
  Dim tbl As ListObject
  Dim rDueDateCol As Range, rUpdateCol As Range

  Set tbl = ActiveSheet.ListObjects("table3")
  Set rUpdateCol = Range("table3[Update]")
  Set rDueDateCol = Range("table3[Due Date]")
  With tbl.Sort
    With .SortFields
      .Clear
      .Add(rUpdateCol, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 0, 0)
      .Add(rUpdateCol, xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(0, 176, 80)
      .Add rDueDateCol, xlSortOnValues, xlAscending
    End With
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .Apply
  End With
End Sub

Can you clarify just what you are trying to achieve?

Can you confirm that it is an ActiveX Control button, not a Form Control button?

IT WORKED thank you so much Peter! I just work on active sheet daily and 1 manager needs sorting the first way and the other one needs the second way and yes its activeX control button but can i tell me how to hide them for printing reasons

 

[Peter_SSs]

tell me how to hide them for printing reasons

Developer ribbon tab -> Design Mode -> Right click the button -> Properties -> PrintObject -> Set to False -> Close Properties window -> click Design Mode again to turn it off