Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort code not working in 07
Hi,
I have a simple function causing a major headache. I am using Excel 2007 and have created a table which can be added to (the additions are done via code attached to a button so they are always in same format). I have then placed a control button at the top of each column to give the user an easy sort function. Each button has the code to sort it's respective column. The problem is that I have this working in 07 no problem but when an 03 user opens the book and tries to run this code they get an error. To solve this I redid the macro code in 03 and used this code instead. This works fine in 07 until there are more than four lines and then I get an error. Most confusing. To build the code I have basically 'recorded macro' performed the necessary sort and pinched the code. Mainly because I don't have enough knowledge to write from scratch. My 07 code that won't work in 03 is: Private Sub CommandButton1_Click() Range("C8:G407").Select ActiveSheet.Sort.SortFields.Clear ActiveSheet.Sort.SortFields.Add Key:=Range("C8"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveSheet.Sort .SetRange Range("C9:G407") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("D3").Select End Sub My 03 code that won't work in 07 is: Range("C9:G407").Select Selection.Sort Key1:=Range("C9"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub With the 03 code I get the error message 'Run Time Error 1004: Sort method of Range class failed' Both 07 and 03 users need to use this same book so I need a code that will work in both, thanks for any help you can offer. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort code not working in 07
Hi Pyrite,
I do a very similar thing in one of my workbooks. When the user clicks on a header title I call the routine below from within the SelectionChange event. It works fine in both 03 and 07 versions of Excel. Beware wrap-around... Public Sub DoSort(ByRef rngData As Range, ByRef rngKey As Range, Optional ByVal Hdr As Long, Optional ByVal Ordr As Long) ' Hdr... ' 0 = xlGuess ' 1 = xlYes ' 2 = xlNo On Error Resume Next If Ordr = 0 Then Ordr = 1 'xlAscending by default rngData.Sort Key1:=rngKey, Order1:=Ordr, Header:=Hdr, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers End Sub Assuming that your CommandButton lives in the Worksheet module, you would call this routine from your CommandButton#_Click routines like so... Call DoSort(Me.Range("C9:G407"), Me.Range("C9")) HTH, Nick H |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort not working | Excel Worksheet Functions | |||
SORT not working | Excel Programming | |||
Data Sort NOT working... | Excel Discussion (Misc queries) | |||
Sort not working | Excel Programming | |||
Sort Left to Right not working | Excel Discussion (Misc queries) |