Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sort one sheet display another
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sort one sheet display another
try putting your sort1 and sort2 macros in a regular module and have the
sheet activate sort1 sort2 -- Don Guillett SalesAid Software "Pete" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sort one sheet display another
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 -- Regards, Tom Ogilvy "Pete" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sort one sheet display another
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. Pete wrote: 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? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to lookup a sheet, number, then display in origonal sheet | New Users to Excel | |||
Updating workbook with an alpha sort sheet and a numeric sort shee | Excel Discussion (Misc queries) | |||
Sort sheet based on particuilar sort order | Excel Worksheet Functions | |||
way to automaitic sort sheet one numerical, sheet two alpha? | Excel Worksheet Functions | |||
Dynamically display subset rows from one sheet in another sheet? | Excel Programming |