VBA Sort one sheet display another

[Pete]

sheet1 has first names and last names data on it.
i have three sort macros which all are working for sheet1.

sheet2 sheet3 and sheet4 have the linked data from sheet1.

here is what i want to do. When a user clicks on sheet tab 2, i would
like the data on sheet1 sorted with sort1. and after the sort display
sheet2.

if the user clicks on sheet3 i want the data on sheet1 sorted with sort2.
and after the sort display sheet2. etc.....

when I do this

Private Sub Worksheet_Activate()
sort1
Worksheets("sheet2").Activate
End Sub

well that gives me and endless loop of sorting of sheet1.

i thought there was a way around this but I can not remember how to do
it.

anyone know the work around for this?

 

[Don Guillett]

try putting your sort1 and sort2 macros in a regular module and have the
sheet activate

sort1
sort2

 

[Tom Ogilvy]

Assume your sort macros are activating sheet1 (which isn't necessary and
eliminating that approach would be the best solution, but in the interim).

Private Sub Worksheet_Activate()
On Error goto ErrHandler
Application.EnableEvents = False
sort1
Worksheets("sheet2").Activate
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Dave Peterson]

I'm guessing your sort routines include an .activate or .select.

Maybe you could rewrite them so that you don't have to activate/select.

I put this code behind sheet2:

Option Explicit
Private Sub Worksheet_Activate()
With Worksheets("sheet1")
.Range("a1:g23").Sort key1:=.Range("a1"), _
order1:=xlAscending, header:=xlNo
End With
End Sub

And it worked ok.

An ugly alternative:

Add some code that disables events--so excel stops looking for triggers to
events.

application.enableevents = false
'do as much as you want
application.enableevents = true

If I were doing this, I'd try to use the first technique. I think it makes the
code easier to read/update later--and if something goes wrong, you don't have to
worry about some way of turning the .enableevents back on.