Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sort
I recorded a macro to perform a sort of a single column on a worksheet then
rewrote the code to perform the same function when a button on a form is pressed. This is the code I used to append a column in a worksheet with a value of a textbox input and then sort the column: Private Sub CommandButton1_Click() 'sngCountAns is the number of entries contained in the target column strAddApt = Me.TextBox1.Value If Len(strAddApt) = 4 Then Sheets("Lists").Range("P" & sngCountAns + 1).Value = strAddApt ActiveWorkbook.Worksheets("Lists").Sort.SortFields .Clear ActiveWorkbook.Worksheets("Lists").Sort.SortFields .Add Key:=Range("P2"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Lists").Sort .SetRange Range("P2:P" & sngCountAns + 1) .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With UserForm_Activate Me.TextBox1.Value = "" Else MsgBox ("Airport ID must be 4 digits to be added to the list") End If End Sub I am using windows vista and Excel 2007. When I press the button the operations performs exactly as I want it to. The file is saved in compatability mode. When the program is opened with a pc using Excel2002, the sort functions generates errors when the CommandButton1 is pressed. I rewrote the code so that it works in Excel 2002 but now it generates errors when I run the program with Excel 2007. Can anyone help me with code that will sort a single column in all versions of excel from 97 to 2007? Thanks. Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sort
Hi Paul,
You say you've written two versions of the code, one that works in each version of Excel. One way you could get around the problem would be to test for the version of Excel that is being used and then write your two pieces of code: Dim intThisVersion As Integer intThisVersion = int(Application.Version) If intThisVersion = 10 Then '2002 version number 'Your 2002 code ElseIf intThisVersion = 12 Then '2007 version number 'Your 2007 code Else 'Other versions End If I don't have 2007 to hand right now, so I can't test the problem for myself, sorry. Sean. "Paul D." wrote: I recorded a macro to perform a sort of a single column on a worksheet then rewrote the code to perform the same function when a button on a form is pressed. This is the code I used to append a column in a worksheet with a value of a textbox input and then sort the column: Private Sub CommandButton1_Click() 'sngCountAns is the number of entries contained in the target column strAddApt = Me.TextBox1.Value If Len(strAddApt) = 4 Then Sheets("Lists").Range("P" & sngCountAns + 1).Value = strAddApt ActiveWorkbook.Worksheets("Lists").Sort.SortFields .Clear ActiveWorkbook.Worksheets("Lists").Sort.SortFields .Add Key:=Range("P2"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Lists").Sort .SetRange Range("P2:P" & sngCountAns + 1) .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With UserForm_Activate Me.TextBox1.Value = "" Else MsgBox ("Airport ID must be 4 digits to be added to the list") End If End Sub I am using windows vista and Excel 2007. When I press the button the operations performs exactly as I want it to. The file is saved in compatability mode. When the program is opened with a pc using Excel2002, the sort functions generates errors when the CommandButton1 is pressed. I rewrote the code so that it works in Excel 2002 but now it generates errors when I run the program with Excel 2007. Can anyone help me with code that will sort a single column in all versions of excel from 97 to 2007? Thanks. Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sort
Sean,
I will do that as a workaround but without every version of excel to test and not sure what version my users will be working with I will either need a version of code for each version of excel or one version that is sure to work on them all. That's the problem with VB programming in excel isn't it? Thanks for the suggestion. Paul "SeanC UK" wrote: Hi Paul, You say you've written two versions of the code, one that works in each version of Excel. One way you could get around the problem would be to test for the version of Excel that is being used and then write your two pieces of code: Dim intThisVersion As Integer intThisVersion = int(Application.Version) If intThisVersion = 10 Then '2002 version number 'Your 2002 code ElseIf intThisVersion = 12 Then '2007 version number 'Your 2007 code Else 'Other versions End If I don't have 2007 to hand right now, so I can't test the problem for myself, sorry. Sean. "Paul D." wrote: I recorded a macro to perform a sort of a single column on a worksheet then rewrote the code to perform the same function when a button on a form is pressed. This is the code I used to append a column in a worksheet with a value of a textbox input and then sort the column: Private Sub CommandButton1_Click() 'sngCountAns is the number of entries contained in the target column strAddApt = Me.TextBox1.Value If Len(strAddApt) = 4 Then Sheets("Lists").Range("P" & sngCountAns + 1).Value = strAddApt ActiveWorkbook.Worksheets("Lists").Sort.SortFields .Clear ActiveWorkbook.Worksheets("Lists").Sort.SortFields .Add Key:=Range("P2"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Lists").Sort .SetRange Range("P2:P" & sngCountAns + 1) .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With UserForm_Activate Me.TextBox1.Value = "" Else MsgBox ("Airport ID must be 4 digits to be added to the list") End If End Sub I am using windows vista and Excel 2007. When I press the button the operations performs exactly as I want it to. The file is saved in compatability mode. When the program is opened with a pc using Excel2002, the sort functions generates errors when the CommandButton1 is pressed. I rewrote the code so that it works in Excel 2002 but now it generates errors when I run the program with Excel 2007. Can anyone help me with code that will sort a single column in all versions of excel from 97 to 2007? Thanks. Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sort
It can be a problem when some parts of the functions are amended between
versions. Sometimes it is possible to leave out certain optional criteria so that the code is OK with all versions, but you would still need to know how the code works in all versions before you would know what to leave out. 2002 and 2003 are fairly similar, so you may find they act the same. I would imagine it's unlikely that many people with still be using 2000 or older, but you never know. Having two pieces of code in front of you, you should be able to see where the differences are, and see if the things that are tripping each version up can be omitted. For instance, using the Find method as it applies to a Range object in 2003, you can use a SearchFormat parameter, but this will trip up in 2000. However, as it is an optional parameter, you can omit it, and then the code works in both versions. Again, not having 2007 with me, I can't test to see exactly where the differences lie, so I can't be of much more help. You could always write some code in the Else part so that anyone that uses your code and doesn't have 2002 or 2007 can notify you. Not an ideal solution though. Sean. "Paul D." wrote: Sean, I will do that as a workaround but without every version of excel to test and not sure what version my users will be working with I will either need a version of code for each version of excel or one version that is sure to work on them all. That's the problem with VB programming in excel isn't it? Thanks for the suggestion. Paul "SeanC UK" wrote: Hi Paul, You say you've written two versions of the code, one that works in each version of Excel. One way you could get around the problem would be to test for the version of Excel that is being used and then write your two pieces of code: Dim intThisVersion As Integer intThisVersion = int(Application.Version) If intThisVersion = 10 Then '2002 version number 'Your 2002 code ElseIf intThisVersion = 12 Then '2007 version number 'Your 2007 code Else 'Other versions End If I don't have 2007 to hand right now, so I can't test the problem for myself, sorry. Sean. "Paul D." wrote: I recorded a macro to perform a sort of a single column on a worksheet then rewrote the code to perform the same function when a button on a form is pressed. This is the code I used to append a column in a worksheet with a value of a textbox input and then sort the column: Private Sub CommandButton1_Click() 'sngCountAns is the number of entries contained in the target column strAddApt = Me.TextBox1.Value If Len(strAddApt) = 4 Then Sheets("Lists").Range("P" & sngCountAns + 1).Value = strAddApt ActiveWorkbook.Worksheets("Lists").Sort.SortFields .Clear ActiveWorkbook.Worksheets("Lists").Sort.SortFields .Add Key:=Range("P2"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Lists").Sort .SetRange Range("P2:P" & sngCountAns + 1) .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With UserForm_Activate Me.TextBox1.Value = "" Else MsgBox ("Airport ID must be 4 digits to be added to the list") End If End Sub I am using windows vista and Excel 2007. When I press the button the operations performs exactly as I want it to. The file is saved in compatability mode. When the program is opened with a pc using Excel2002, the sort functions generates errors when the CommandButton1 is pressed. I rewrote the code so that it works in Excel 2002 but now it generates errors when I run the program with Excel 2007. Can anyone help me with code that will sort a single column in all versions of excel from 97 to 2007? Thanks. Paul |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sort
Sean
Your help and advice is very much appreciated. Paul "SeanC UK" wrote: It can be a problem when some parts of the functions are amended between versions. Sometimes it is possible to leave out certain optional criteria so that the code is OK with all versions, but you would still need to know how the code works in all versions before you would know what to leave out. 2002 and 2003 are fairly similar, so you may find they act the same. I would imagine it's unlikely that many people with still be using 2000 or older, but you never know. Having two pieces of code in front of you, you should be able to see where the differences are, and see if the things that are tripping each version up can be omitted. For instance, using the Find method as it applies to a Range object in 2003, you can use a SearchFormat parameter, but this will trip up in 2000. However, as it is an optional parameter, you can omit it, and then the code works in both versions. Again, not having 2007 with me, I can't test to see exactly where the differences lie, so I can't be of much more help. You could always write some code in the Else part so that anyone that uses your code and doesn't have 2002 or 2007 can notify you. Not an ideal solution though. Sean. "Paul D." wrote: Sean, I will do that as a workaround but without every version of excel to test and not sure what version my users will be working with I will either need a version of code for each version of excel or one version that is sure to work on them all. That's the problem with VB programming in excel isn't it? Thanks for the suggestion. Paul "SeanC UK" wrote: Hi Paul, You say you've written two versions of the code, one that works in each version of Excel. One way you could get around the problem would be to test for the version of Excel that is being used and then write your two pieces of code: Dim intThisVersion As Integer intThisVersion = int(Application.Version) If intThisVersion = 10 Then '2002 version number 'Your 2002 code ElseIf intThisVersion = 12 Then '2007 version number 'Your 2007 code Else 'Other versions End If I don't have 2007 to hand right now, so I can't test the problem for myself, sorry. Sean. "Paul D." wrote: I recorded a macro to perform a sort of a single column on a worksheet then rewrote the code to perform the same function when a button on a form is pressed. This is the code I used to append a column in a worksheet with a value of a textbox input and then sort the column: Private Sub CommandButton1_Click() 'sngCountAns is the number of entries contained in the target column strAddApt = Me.TextBox1.Value If Len(strAddApt) = 4 Then Sheets("Lists").Range("P" & sngCountAns + 1).Value = strAddApt ActiveWorkbook.Worksheets("Lists").Sort.SortFields .Clear ActiveWorkbook.Worksheets("Lists").Sort.SortFields .Add Key:=Range("P2"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Lists").Sort .SetRange Range("P2:P" & sngCountAns + 1) .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With UserForm_Activate Me.TextBox1.Value = "" Else MsgBox ("Airport ID must be 4 digits to be added to the list") End If End Sub I am using windows vista and Excel 2007. When I press the button the operations performs exactly as I want it to. The file is saved in compatability mode. When the program is opened with a pc using Excel2002, the sort functions generates errors when the CommandButton1 is pressed. I rewrote the code so that it works in Excel 2002 but now it generates errors when I run the program with Excel 2007. Can anyone help me with code that will sort a single column in all versions of excel from 97 to 2007? Thanks. Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating workbook with an alpha sort sheet and a numeric sort shee | Excel Discussion (Misc queries) | |||
sort function for dates does not sort properly in Office 2007 Exc. | Excel Worksheet Functions | |||
Pls. reply Sort Data and copy to next coulmn when sort order chang | Excel Programming | |||
Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) | |||
sort (on part of) string - originally posted under Tricky Sort | Excel Programming |