hard coding the a personal list option

Topic

New Reply

I could use the person list sorting option under excel options, but I can not assume that all users of the file will have done so on their computer. Is there a way to do a pivot table sort with a personal sorting criteria for a variable called roomtemp so that is sorted by cold, warm, and hot.

Reply | Quote

Replies

Hi

You could add a custom sort order using VBA.
If the customised sort already exists on their computer, adding it again via VBA doesn’t do anything, so no harm done.
To use any custom sort order in VBA, you need to know where about in the list of all defined custom sorts.
Try modifying the following code to suit (for example, using a named range for your pivot and sort cell etc.):

Code:

Sub sortByCustomList()
Application.addCustomList Array("cold", "warm", "hot", "very hot")
i = Application.GetCustomListNum(Array("cold", "warm", "hot", "very hot"))
[a10].CurrentRegion.Sort Key1:=[a10], _
    Order1:=xlAscending, _
    Header:=xlGuess, _
    OrderCustom:=i, _
    MatchCase:=False, _
    Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
End Sub

zeddy

Reply | Quote

Hi

Another way to sort a column containing entries like cold, warm, hot without using VBA is to simply select the data column, use Ctr-H to bring up the find-and-replace option, then replace warm with dwarm (replace all), then just use the normal alphabetic sort on your data, then after sorting, re-select and replace the dwarm entries back to warm.
Voila! No VBA, no data gets hurt and everyone is happy.

What I like about Excel is there are always lots of ways to achieve a result.
And I can think of several more ways to do what you ask.
Perhaps others may chip in their ideas too.

zeddy

Reply | Quote

For only three items you could of course just drag them into the order you want.

Reply | Quote