help with vba/Macro sorting

[Maggie]

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!!

 

[Ross Culver]

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

 

[Tom Hutchins]

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]

That worked perfectly! Thank you so much!!

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