Modifying code

[shimaa01234]

This code for Mister "MickG"
I want it works Automatic without "commandbutton"

Private Sub CommandButton1_Click()
Dim Ans As String
Dim oSort As String
Ans = MsgBox("""No"" = Male sort" & Chr(10) & """Yes"" = Female sort", vbYesNo + vbInformation)
oSort = IIf(Ans = vbYes, xlAscending, xlDescending)
Range("A2:D16").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1:D16").Select
Selection.Sort Key1:=Range("D2"), Order1:=oSort, Key2:=Range("A2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End Sub

 

[shimaa01234]

You could sort of work after the completion of writing data and pressure on the cell "A1"

 

[Michael M]

Place this in the Sheet module

Private Sub worksheet_change(ByVal target As Range)
Dim Ans As String, oSort As String
If Intersect(target, Range("A1")) Is Nothing Then Exit Sub
Ans = MsgBox("""No"" = Male sort" & Chr(10) & """Yes"" = Female sort", vbYesNo + vbInformation)
oSort = IIf(Ans = vbYes, xlAscending, xlDescending)
Range("A2:D16").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1:D16").Select
Selection.Sort Key1:=Range("D2"), Order1:=oSort, Key2:=Range("A2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End Sub

 

[shimaa01234]

Give me "Run-time error "1004" "
The shade of this part of the code in yellow

Selection.Sort Key1:=Range("D2"), Order1:=oSort, Key2:=Range("A2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal

I am using Office 2003
Maybe this is why you receive this message