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]

I want to sort the table automatically,
Names are sorted alphabetically
And Gender are sorted "male" first
[TABLE="width: 676"]
<colgroup><col><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD]id[/TD]
[TD]Student Code[/TD]
[TD]Student Name[/TD]
[TD]National ID[/TD]
[TD]Date of Birth[/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[TD]Gender[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]462037065[/TD]
[TD]G[/TD]
[TD]123457[/TD]
[TD]10/08/2008[/TD]
[TD]21[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]MALE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]462036994[/TD]
[TD]I[/TD]
[TD]123457[/TD]
[TD]01/05/2007[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]462614969[/TD]
[TD]J[/TD]
[TD]123457[/TD]
[TD]15/12/2007[/TD]
[TD]16[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]462036728[/TD]
[TD]K[/TD]
[TD]123457[/TD]
[TD]30/08/2008[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]462038314[/TD]
[TD]L[/TD]
[TD]123457[/TD]
[TD]27/09/2008[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]462037445[/TD]
[TD]M[/TD]
[TD]123457[/TD]
[TD]19/05/2008[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]462037529[/TD]
[TD]N[/TD]
[TD]123457[/TD]
[TD]27/10/2007[/TD]
[TD]4[/TD]
[TD]11[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]462036415[/TD]
[TD]O[/TD]
[TD]123457[/TD]
[TD]01/07/2008[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]461818730[/TD]
[TD]P[/TD]
[TD]123457[/TD]
[TD]28/06/2008[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]462390586[/TD]
[TD]R[/TD]
[TD]123457[/TD]
[TD]20/04/2008[/TD]
[TD]11[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]462037901[/TD]
[TD]FF[/TD]
[TD]123457[/TD]
[TD]11/05/2008[/TD]
[TD]20[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]461884605[/TD]
[TD]GG[/TD]
[TD]123457[/TD]
[TD]21/09/2008[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]MALE[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]462614878[/TD]
[TD]HH[/TD]
[TD]123457[/TD]
[TD]13/08/2008[/TD]
[TD]18[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]MALE[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]462296395[/TD]
[TD]II[/TD]
[TD]123457[/TD]
[TD]21/06/2008[/TD]
[TD]10[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]MALE[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]462456546[/TD]
[TD]JJ[/TD]
[TD]123457[/TD]
[TD]01/09/2008[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]MALE[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]462036647[/TD]
[TD]LL[/TD]
[TD]123457[/TD]
[TD]01/10/2008[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]MALE[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]461730442[/TD]
[TD]MM[/TD]
[TD]123457[/TD]
[TD]01/11/2007[/TD]
[TD]0[/TD]
[TD]11[/TD]
[TD]6[/TD]
[TD]MALE[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]462614924[/TD]
[TD]NN[/TD]
[TD]123457[/TD]
[TD]20/12/2007[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]MALE[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]462038000[/TD]
[TD]OO[/TD]
[TD]123457[/TD]
[TD]29/09/2008[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]462003161[/TD]
[TD]QQ[/TD]
[TD]123457[/TD]
[TD]04/01/2008[/TD]
[TD]27[/TD]
[TD]8[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]462038126[/TD]
[TD]RR[/TD]
[TD]123457[/TD]
[TD]05/01/2008[/TD]
[TD]26[/TD]
[TD]8[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]462036842[/TD]
[TD]A[/TD]
[TD]123457[/TD]
[TD]20/04/2008[/TD]
[TD]11[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]462036216[/TD]
[TD]B[/TD]
[TD]123457[/TD]
[TD]05/11/2007[/TD]
[TD]26[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]462038261[/TD]
[TD]C[/TD]
[TD]123457[/TD]
[TD]25/09/2008[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]462037299[/TD]
[TD]D[/TD]
[TD]123457[/TD]
[TD]28/06/2008[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]461837278[/TD]
[TD]E[/TD]
[TD]123457[/TD]
[TD]01/05/2008[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]MALE[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]461837278[/TD]
[TD]F[/TD]
[TD]123457[/TD]
[TD]14/08/2008[/TD]
[TD]17[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]MALE[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]462614593[/TD]
[TD]H[/TD]
[TD]123457[/TD]
[TD]17/09/2007[/TD]
[TD]14[/TD]
[TD]0[/TD]
[TD]7[/TD]
[TD]MALE[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]462390586[/TD]
[TD]Q[/TD]
[TD]123457[/TD]
[TD]05/08/2008[/TD]
[TD]26[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]MALE[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]462037601[/TD]
[TD]S[/TD]
[TD]123457[/TD]
[TD]25/02/2008[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]MALE[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]462295956[/TD]
[TD]T[/TD]
[TD]123457[/TD]
[TD]07/05/2008[/TD]
[TD]24[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]MALE[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]461864916[/TD]
[TD]U[/TD]
[TD]123457[/TD]
[TD]17/04/2008[/TD]
[TD]14[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]461798036[/TD]
[TD]V[/TD]
[TD]123457[/TD]
[TD]01/12/2007[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]462522088[/TD]
[TD]W[/TD]
[TD]123457[/TD]
[TD]11/06/2008[/TD]
[TD]20[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]462037669[/TD]
[TD]X[/TD]
[TD]123457[/TD]
[TD]10/04/2008[/TD]
[TD]21[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]462037732[/TD]
[TD]Y[/TD]
[TD]123457[/TD]
[TD]15/11/2007[/TD]
[TD]16[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]462037795[/TD]
[TD]Z[/TD]
[TD]123457[/TD]
[TD]11/06/2008[/TD]
[TD]20[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]461730407[/TD]
[TD]AA[/TD]
[TD]123457[/TD]
[TD]25/02/2008[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]462037858[/TD]
[TD]BB[/TD]
[TD]123457[/TD]
[TD]01/10/2008[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]461884728[/TD]
[TD]CC[/TD]
[TD]123457[/TD]
[TD]05/03/2008[/TD]
[TD]26[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]462296006[/TD]
[TD]DD[/TD]
[TD]123457[/TD]
[TD]01/08/2007[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD]462295924[/TD]
[TD]EE[/TD]
[TD]123457[/TD]
[TD]27/10/2007[/TD]
[TD]4[/TD]
[TD]11[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]462114486[/TD]
[TD]KK[/TD]
[TD]123457[/TD]
[TD]01/01/2008[/TD]
[TD]0[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]Female[/TD]
[/TR]
</tbody>[/TABLE]

 

[shimaa01234]

up

 

[shimaa01234]

Any help to work this code

 

[dirtychinchilla]

This is a funny thread "Mister Michael" is asking you which cell you want to activate the macro, because without knowing exactly which cell you are going to change, he will not be able to give you code to copy and paste with. So, just tell him which cell it is that you are entering yes or no into.

 

[Darren Bartrup]

Going to take a guess here - when a new record is entered completely.
i.e. The id, Student Code, Student Name, National ID, Date of Birth, the three numbers and gender have all been entered the code should fire and sort the list.

So on the Worksheet_Change event each cell on the current row needs to be checked for valid data and then the code fires.

 

[shimaa01234]

Thanks for commenting Mister !!!!!!!!
I said it before
I need a "sort column names" alphabetically
And sort the column "Gender" by making male first
At the same table

 

[Darren Bartrup]

You've said you wanted it sorted, but not when - previously it was on the click of a button, but now it could be when you open the file, when you change sheets or any number of other events that could happen - the event in this case is when you change the value in a cell on the worksheet.

This code checks the row (not very well, but it checks it) and fires your code if everything is ok.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lRow As Long
    
    lRow = Target.Row
    
    With ActiveSheet
        If IsNumeric(.Cells(lRow, 1)) And _
           IsNumeric(.Cells(lRow, 2)) And _
           Len(.Cells(lRow, 2)) = 9 And _
           .Cells(lRow, 3) <> "" And _
           IsNumeric(.Cells(lRow, 4)) And _
           Len(.Cells(lRow, 4)) = 7 And _
           IsDate(.Cells(lRow, 5)) And _
           IsNumeric(.Cells(lRow, 6)) And _
           IsNumeric(.Cells(lRow, 7)) And _
           IsNumeric(.Cells(lRow, 8)) And _
           (UCase(.Cells(lRow, 9)) = "MALE" Or UCase(.Cells(lRow, 9)) = "FEMALE") Then
           
                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 If
    End With
    
End Sub

Is that what you're after?

 

[shimaa01234]

First - thank you very much Mister "Darren"
On the interest, but unfortunately the code did not work, despite the modulatory columns of numbers, which are sorting
It is the column number "C2", which contains the names
The column "I2" which contains a "Gender"
Maybe because I use Office 2003
I changed the code in these columns and in spite of it did not work

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("B2:I44").Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B1:I44").Select
Selection.Sort Key1:=Range("I2"), Order1:=oSort, Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal

 

[Michael M]

What do you mean by

and in spite of it did not work

I have asked for information, Darren has asked the same question and still, you fail to see what we require to help you.


You need to answer this question !!!!

You've said you wanted it sorted, but not when - previously it was on the click of a button, but now it could be when you open the file, when you change sheets or any number of other events that could happen

 

[shimaa01234]

I am very sorry Mister "MichaelM"
Did not pay attention to the question well !!!!!!!!! I'm sorry again ، I want to change happen
when you change sheets or any number of other events that could happen