SarahMS1

New Member
Joined
Jan 29, 2020
Messages
20
Office Version
2016
Platform
Windows
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
681
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
Are you adding the CustomSort for way 1 to top above colors and do you have an example of what you're doing for sorts already?
 

SarahMS1

New Member
Joined
Jan 29, 2020
Messages
20
Office Version
2016
Platform
Windows
can you explain what do u mean by adding custom sort for way 1 above colors? and what im doint for my sorts is changing the custom sorts manually everytime either i want way 1 or two i have to do it from sort & filter thats why i need 2 buttons to make it easier instead of going back and forth
 

SarahMS1

New Member
Joined
Jan 29, 2020
Messages
20
Office Version
2016
Platform
Windows
Are you adding the CustomSort for way 1 to top above colors and do you have an example of what you're doing for sorts already?
can you explain what do u mean by adding custom sort for way 1 above colors? and what im doint for my sorts is changing the custom sorts manually everytime either i want way 1 or two i have to do it from sort & filter thats why i need 2 buttons to make it easier instead of going back and forth
 

SarahMS1

New Member
Joined
Jan 29, 2020
Messages
20
Office Version
2016
Platform
Windows
i have to mention that my data is in a table
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
681
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
So can u provide the header items you are sorting on along with the table name and sort orders? More detail will help. Do you want the ability to store the custom sort list somewhere in the workbook? Will look at this more later today off 2 bed for now...
i have to mention that my data is in a table
 

SarahMS1

New Member
Joined
Jan 29, 2020
Messages
20
Office Version
2016
Platform
Windows
So can u provide the header items you are sorting on along with the table name and sort orders? More detail will help. Do you want the ability to store the custom sort list somewhere in the workbook? Will look at this more later today off 2 bed for now...
for the the first button the header name of coloumn i am sorting is ‘Deadline’ which is placed on column M the sort order is (24 H, 48 H, 3 days, 4 days, 5 days, 1 week, 2 weeks, 1 month, 2 months) table name is table3 and yes the button i’ll place it on the same sheet column T because its empty and i’ll just hide and unhide it for printing
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,415
Office Version
365
Platform
Windows
For way2 it is unclear
- whether the colour is also in column N (I have assumed so)
- whether the colour is font colour or background colour (I have assumed font colour)
- what the heading of that column is (I have assumed "Date")

Anyway, see if these head you in the right direction

VBA Code:
Sub SortDeadlineCustom_Click()
  Dim tbl As ListObject
  Const sSortOrder As String = "24 H,48 H,3 days,4 days,5 days,1 week,2 weeks,1 month,2 months,monthly,yearly"
  
  Set tbl = ActiveSheet.ListObjects("table3")
  With tbl.Sort
    .SortFields.Clear
    .SortFields.Add Key:=tbl.ListColumns("Deadline").DataBodyRange, Order:=xlAscending, CustomOrder:="""," & sSortOrder & """"
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .Apply
  End With
End Sub

VBA Code:
Sub SortDateCustom_Click()
  Dim tbl As ListObject
  Dim rDateCol As Range
  
  Set tbl = ActiveSheet.ListObjects("table3")
  Set rDateCol = Range("table3[Date]")
  With tbl.Sort
    With .SortFields
      .Clear
      .Add(rDateCol, xlSortOnFontColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 0, 0)
      .Add(rDateCol, xlSortOnFontColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(0, 176, 80)
      .Add rDateCol, xlSortOnValues, xlAscending
    End With
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .Apply
  End With
End Sub
 

SarahMS1

New Member
Joined
Jan 29, 2020
Messages
20
Office Version
2016
Platform
Windows
For way2 it is unclear
- whether the colour is also in column N (I have assumed so)
- whether the colour is font colour or background colour (I have assumed font colour)
- what the heading of that column is (I have assumed "Date")

Anyway, see if these head you in the right direction

VBA Code:
Sub SortDeadlineCustom_Click()
  Dim tbl As ListObject
  Const sSortOrder As String = "24 H,48 H,3 days,4 days,5 days,1 week,2 weeks,1 month,2 months,monthly,yearly"
 
  Set tbl = ActiveSheet.ListObjects("table3")
  With tbl.Sort
    .SortFields.Clear
    .SortFields.Add Key:=tbl.ListColumns("Deadline").DataBodyRange, Order:=xlAscending, CustomOrder:="""," & sSortOrder & """"
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .Apply
  End With
End Sub

VBA Code:
Sub SortDateCustom_Click()
  Dim tbl As ListObject
  Dim rDateCol As Range
 
  Set tbl = ActiveSheet.ListObjects("table3")
  Set rDateCol = Range("table3[Date]")
  With tbl.Sort
    With .SortFields
      .Clear
      .Add(rDateCol, xlSortOnFontColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 0, 0)
      .Add(rDateCol, xlSortOnFontColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(0, 176, 80)
      .Add rDateCol, xlSortOnValues, xlAscending
    End With
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .Apply
  End With
End Sub

thank you so much for ur amazing effort, for button 1 it worked perfectly just the way i wanted for button 2 however it didnt but thats all on me because i didnt explain well enough i’ll just attach a photo for what i want the button to do exactly as the custom sort i did also to mention that ‘Update’ is on column N
and ‘Due Date’ on M.
the red color is at (255, 204,204)
the green color is at (226,239,218)

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
 

SarahMS1

New Member
Joined
Jan 29, 2020
Messages
20
Office Version
2016
Platform
Windows
E5398190-B265-4518-ACEC-CEF2661E45C5.jpeg
my attachment and forgot to mention its cell color
 

Watch MrExcel Video

Forum statistics

Threads
1,095,354
Messages
5,443,966
Members
405,257
Latest member
daveyf

This Week's Hot Topics

Top