Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with vba/Macro sorting
Hi, I am trying to set up a worksheet that will automatically sort a record
by date and order number as soon as it's entered. I figured out a Macro to do that, but it sorts it as soon as you enter the date, and I need it to wait until all fields have been filled in. Can someone help me modify this code or suggest something different I can try? Right now, when I enter the date, I then have to scroll up to where it sorted to and fill in the rest of the info. Here's what I have: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A8:J75").Sort Key1:=Range("B9"), Order1:=xlAscending, Key2:=Range( _ "C9"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal End Sub I really don't know much about vba, so when you answer, you're going to have to make it as dumbed down as possible. Thank you so much in advance!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with vba/Macro sorting
You could put your code into a controllable event, like a button click. Or
just run the code whenever you're ready using hot keys. Ross "Maggie" wrote in message ... Hi, I am trying to set up a worksheet that will automatically sort a record by date and order number as soon as it's entered. I figured out a Macro to do that, but it sorts it as soon as you enter the date, and I need it to wait until all fields have been filled in. Can someone help me modify this code or suggest something different I can try? Right now, when I enter the date, I then have to scroll up to where it sorted to and fill in the rest of the info. Here's what I have: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A8:J75").Sort Key1:=Range("B9"), Order1:=xlAscending, Key2:=Range( _ "C9"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal End Sub I really don't know much about vba, so when you answer, you're going to have to make it as dumbed down as possible. Thank you so much in advance!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with vba/Macro sorting
Maybe your macro can check all the relevant cells in the current row. If any
are still empty, don't sort yet. For example: Private Sub Worksheet_Change(ByVal Target As Range) Dim SortNow As Boolean 'Initialize SortNow as TRUE SortNow = True 'If not in column A through J, do nothing If Intersect(Target, Columns("A:J")) Is Nothing Then Exit Sub 'Check all the required cells. If any are empty, set 'SortNow to FALSE. If Len(Cells(Target.Row, 1).Value) = 0 Then SortNow = False If Len(Cells(Target.Row, 2).Value) = 0 Then SortNow = False If Len(Cells(Target.Row, 3).Value) = 0 Then SortNow = False If Len(Cells(Target.Row, 4).Value) = 0 Then SortNow = False If Len(Cells(Target.Row, 5).Value) = 0 Then SortNow = False If Len(Cells(Target.Row, 6).Value) = 0 Then SortNow = False If Len(Cells(Target.Row, 7).Value) = 0 Then SortNow = False If Len(Cells(Target.Row, 8).Value) = 0 Then SortNow = False If Len(Cells(Target.Row, 9).Value) = 0 Then SortNow = False If Len(Cells(Target.Row, 10).Value) = 0 Then SortNow = False 'Etc. for whichever columns are required If SortNow = True Then 'Sort the data Range("A8:J75").Sort Key1:=Range("B9"), Order1:=xlAscending, Key2:=Range( _ "C9"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal End If End Sub This event code would go in the sheet code page where you have your current event macro. Comment out your existing macro by putting an apostrophe at the beginning of every line while you test the new macro. As written, the sort won't get triggered until something is entered in every cell in a row in columns A through J. Adjust by adding or deleting "If Len(Cells(Target.Row," statements. The number following Target.Row is the column number. Hope this helps, Hutch "Maggie" wrote: Hi, I am trying to set up a worksheet that will automatically sort a record by date and order number as soon as it's entered. I figured out a Macro to do that, but it sorts it as soon as you enter the date, and I need it to wait until all fields have been filled in. Can someone help me modify this code or suggest something different I can try? Right now, when I enter the date, I then have to scroll up to where it sorted to and fill in the rest of the info. Here's what I have: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A8:J75").Sort Key1:=Range("B9"), Order1:=xlAscending, Key2:=Range( _ "C9"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal End Sub I really don't know much about vba, so when you answer, you're going to have to make it as dumbed down as possible. Thank you so much in advance!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with vba/Macro sorting
That worked perfectly! Thank you so much!!
"Tom Hutchins" wrote: Maybe your macro can check all the relevant cells in the current row. If any are still empty, don't sort yet. For example: Private Sub Worksheet_Change(ByVal Target As Range) Dim SortNow As Boolean 'Initialize SortNow as TRUE SortNow = True 'If not in column A through J, do nothing If Intersect(Target, Columns("A:J")) Is Nothing Then Exit Sub 'Check all the required cells. If any are empty, set 'SortNow to FALSE. If Len(Cells(Target.Row, 1).Value) = 0 Then SortNow = False If Len(Cells(Target.Row, 2).Value) = 0 Then SortNow = False If Len(Cells(Target.Row, 3).Value) = 0 Then SortNow = False If Len(Cells(Target.Row, 4).Value) = 0 Then SortNow = False If Len(Cells(Target.Row, 5).Value) = 0 Then SortNow = False If Len(Cells(Target.Row, 6).Value) = 0 Then SortNow = False If Len(Cells(Target.Row, 7).Value) = 0 Then SortNow = False If Len(Cells(Target.Row, 8).Value) = 0 Then SortNow = False If Len(Cells(Target.Row, 9).Value) = 0 Then SortNow = False If Len(Cells(Target.Row, 10).Value) = 0 Then SortNow = False 'Etc. for whichever columns are required If SortNow = True Then 'Sort the data Range("A8:J75").Sort Key1:=Range("B9"), Order1:=xlAscending, Key2:=Range( _ "C9"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal End If End Sub This event code would go in the sheet code page where you have your current event macro. Comment out your existing macro by putting an apostrophe at the beginning of every line while you test the new macro. As written, the sort won't get triggered until something is entered in every cell in a row in columns A through J. Adjust by adding or deleting "If Len(Cells(Target.Row," statements. The number following Target.Row is the column number. Hope this helps, Hutch "Maggie" wrote: Hi, I am trying to set up a worksheet that will automatically sort a record by date and order number as soon as it's entered. I figured out a Macro to do that, but it sorts it as soon as you enter the date, and I need it to wait until all fields have been filled in. Can someone help me modify this code or suggest something different I can try? Right now, when I enter the date, I then have to scroll up to where it sorted to and fill in the rest of the info. Here's what I have: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A8:J75").Sort Key1:=Range("B9"), Order1:=xlAscending, Key2:=Range( _ "C9"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal End Sub I really don't know much about vba, so when you answer, you're going to have to make it as dumbed down as possible. Thank you so much in advance!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting using macro | Excel Discussion (Misc queries) | |||
Sorting in a macro | Excel Programming | |||
macro for sorting | Excel Discussion (Misc queries) | |||
Macro for Sorting | Excel Programming | |||
Sorting with a macro? | Excel Programming |