Prev: How to set Excel 2010 as default for open excel files instead of 2
Next: date problem
From: ATChurch on 16 Mar 2010 05:46
Hi. I have created a custom sort order by using a list in a hidden sheet in a
workbook. This is then followed by two default sorts.

I recorded a macro of this sort being performed on each of the sheets in the
work book and then used the Visual Basic edittor to make it run each time the
workbook is opened.

The problem is, this works perfectly for me, but when I emailed it to my
collegues who will also use it, the custom sort had reverted to a default
(alphabetical) sort.

Further, when they email me back the edited table, the macro works fine when
I open it.

What's going on?

PS: The custom sort is for a table containing a "High/Medium/Low" rating
column and it is instead sorting to "High/Low/Medium".
From: Jacob Skaria on 16 Mar 2010 08:25
Custom lists are added to your computer registry..Check out the below link

http://office.microsoft.com/en-us/excel/HA102221421033.aspx

--
Jacob


"ATChurch" wrote:

> Hi. I have created a custom sort order by using a list in a hidden sheet in a
> workbook. This is then followed by two default sorts.
>
> I recorded a macro of this sort being performed on each of the sheets in the
> work book and then used the Visual Basic edittor to make it run each time the
> workbook is opened.
>
> The problem is, this works perfectly for me, but when I emailed it to my
> collegues who will also use it, the custom sort had reverted to a default
> (alphabetical) sort.
>
> Further, when they email me back the edited table, the macro works fine when
> I open it.
>
> What's going on?
>
> PS: The custom sort is for a table containing a "High/Medium/Low" rating
> column and it is instead sorting to "High/Low/Medium".
From: Dave Peterson on 16 Mar 2010 08:49
Maybe you could add the customlist in code, do the sort, and remove the
customlist.

Maybe you can add the customlist, use it and delete it all in code:

Option Explicit
Sub testme01()

Dim myArr As Variant
Dim myListNumber As Long
Dim myRng As Range

With Worksheets("Hidden")
myArr = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
End With

'or lose the hidden sheet and do it all in code???
'myArr = Array("Grape", "Apple", "Orange", "Banana", "Melon")

Application.AddCustomList listarray:=myArr
myListNumber = Application.GetCustomListNum(myArr)

Set myRng = Worksheets("Sheet999").Range("a1:k30")

With myRng
.Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=myListNumber + 1
End With

Application.DeleteCustomList myListNumber

End Sub



ATChurch wrote:
>
> Hi. I have created a custom sort order by using a list in a hidden sheet in a
> workbook. This is then followed by two default sorts.
>
> I recorded a macro of this sort being performed on each of the sheets in the
> work book and then used the Visual Basic edittor to make it run each time the
> workbook is opened.
>
> The problem is, this works perfectly for me, but when I emailed it to my
> collegues who will also use it, the custom sort had reverted to a default
> (alphabetical) sort.
>
> Further, when they email me back the edited table, the macro works fine when
> I open it.
>
> What's going on?
>
> PS: The custom sort is for a table containing a "High/Medium/Low" rating
> column and it is instead sorting to "High/Low/Medium".

--

Dave Peterson
 | 
Pages: 1
Prev: How to set Excel 2010 as default for open excel files instead of 2
Next: date problem