Automatic sort in another sheet

[rebelroy]

Hi all,

My 2003 workbook has two sheets - 'Data' and 'Front'

The user enters data in five columns - A-E in the 'data' sheet. The entered data is a dynamic named range called 'members' which grows/shrinks according as data is entered/deleted. A cell in the 'Front' sheet is validated to show the 'members' list.

I've provided a button in the data sheet to sort the data using the following code recorded with the macro recorder which works fine.

Private Sub CommandButton1_Click()
 
Range("A5:E5").Select
    Range(Selection, Selection.End(xlDown)).Select

Sheet2.Unprotect "password"

    Selection.Sort Key1:=Range("E5"), Order1:=xlAscending, Key2:=Range("D5") _
        , Order2:=xlAscending, Key3:=Range("B5"), Order3:=xlDescending, Header _
        :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
        , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal

Sheet2.Protect "password"
 
End Sub

In order to provide for a situation where the user forgets to sort the data manually by using the button, I want the data to sort automatically when the 'Front' sheet is activated.

How can this be done please?

 

[GTO]

Not tested, but as it appears this is happening on (codename) Sheet2, maybe:

In the worksheet's module:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>    <SPAN style="color:#00007F">Call</SPAN> SortMe<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> SortMe()<br><SPAN style="color:#00007F">Dim</SPAN> lLRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>    <SPAN style="color:#00007F">With</SPAN> Sheet2<br>        .Unprotect "password"<br>        <br>        lLRow = .Range("A:E").Find("*", .Range("A1"), xlValues, xlPart, xlByRows, xlPrevious).Row<br>        <br>        .Range("A5:E" & lLRow).Sort Key1:=.Range("E5"), Order1:=xlAscending, Key2:=.Range("D5") _<br>        , Order2:=xlAscending, Key3:=.Range("B5"), Order3:=xlDescending, Header _<br>        :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _<br>        , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _<br>        xlSortNormal<br>        <br>        .Protect "password"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Deactivate()<br>    <SPAN style="color:#00007F">Call</SPAN> SortMe<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Hope this helps,

Mark

 

[rebelroy]

Great job Mark, thanks.