SarahMS1

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

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
E5398190-B265-4518-ACEC-CEF2661E45C5.jpeg
my attachment and forgot to mention its cell color
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top