Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Row macro works in 2002 but not 2003
Good Day, I have been from site to site to get an answer and I am stumped...
I am usiing the following code: Private Sub Worksheet_Calculate() Dim x As Integer Application.ScreenUpdating = False Cells.EntireRow.Hidden = False 'unhide everything For x = 51 To 79 If Range("U" & x).Value = 0 Then Rows(x).EntireRow.Hidden = True End If Next x Application.ScreenUpdating = True End Sub The purpose and background is that the workbook has 9 sheets. This code is being utilized on 2 of the sheets. The macro was created in Excel 2002 and when i try the file in Excel 2003 I get screen flickers and the file crashes. When i hit CTRL + Pause the program skips to the debugger and hilights either End If or End Sub, i guess somewhere in the loop it has caused? In the range "U51:U79" there is a formula (HLOOKUP - for sheete 5 & 7 ) that will display a result of either a 1 or a 0 depending on what item is chosen on sheet 1. If it is a 0 then i would like the macro to hide the rows that are 0. Another note is that this macro works with the sheets all password protected and i allow rows to be formated when I set the password. (i have tried unprotecting the sheet in Excel 2003, still does not work) Again the Worksheet works perfectly in Excel 2002 but crashes in Excel 2003. Sorry for the long winded note but i was reading several other posts and wanted to try to give as much info so we are not reposting Q&A about the spreadsheet functions. I hope I left enough info. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Row macro works in 2002 but not 2003
Gooser,
That code tested just fine for me in xl2003. Windows XP home. Try runnning the code in break mode and see where the error occurs exactly "Gooser555" wrote: Good Day, I have been from site to site to get an answer and I am stumped... I am usiing the following code: Private Sub Worksheet_Calculate() Dim x As Integer Application.ScreenUpdating = False Cells.EntireRow.Hidden = False 'unhide everything For x = 51 To 79 If Range("U" & x).Value = 0 Then Rows(x).EntireRow.Hidden = True End If Next x Application.ScreenUpdating = True End Sub The purpose and background is that the workbook has 9 sheets. This code is being utilized on 2 of the sheets. The macro was created in Excel 2002 and when i try the file in Excel 2003 I get screen flickers and the file crashes. When i hit CTRL + Pause the program skips to the debugger and hilights either End If or End Sub, i guess somewhere in the loop it has caused? In the range "U51:U79" there is a formula (HLOOKUP - for sheete 5 & 7 ) that will display a result of either a 1 or a 0 depending on what item is chosen on sheet 1. If it is a 0 then i would like the macro to hide the rows that are 0. Another note is that this macro works with the sheets all password protected and i allow rows to be formated when I set the password. (i have tried unprotecting the sheet in Excel 2003, still does not work) Again the Worksheet works perfectly in Excel 2002 but crashes in Excel 2003. Sorry for the long winded note but i was reading several other posts and wanted to try to give as much info so we are not reposting Q&A about the spreadsheet functions. I hope I left enough info. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide Row macro works in 2002 but not 2003
Not sure how to run break mode. Did you try with fomula reference runing in
the cells the macro references "ben" wrote: Gooser, That code tested just fine for me in xl2003. Windows XP home. Try runnning the code in break mode and see where the error occurs exactly "Gooser555" wrote: Good Day, I have been from site to site to get an answer and I am stumped... I am usiing the following code: Private Sub Worksheet_Calculate() Dim x As Integer Application.ScreenUpdating = False Cells.EntireRow.Hidden = False 'unhide everything For x = 51 To 79 If Range("U" & x).Value = 0 Then Rows(x).EntireRow.Hidden = True End If Next x Application.ScreenUpdating = True End Sub The purpose and background is that the workbook has 9 sheets. This code is being utilized on 2 of the sheets. The macro was created in Excel 2002 and when i try the file in Excel 2003 I get screen flickers and the file crashes. When i hit CTRL + Pause the program skips to the debugger and hilights either End If or End Sub, i guess somewhere in the loop it has caused? In the range "U51:U79" there is a formula (HLOOKUP - for sheete 5 & 7 ) that will display a result of either a 1 or a 0 depending on what item is chosen on sheet 1. If it is a 0 then i would like the macro to hide the rows that are 0. Another note is that this macro works with the sheets all password protected and i allow rows to be formated when I set the password. (i have tried unprotecting the sheet in Excel 2003, still does not work) Again the Worksheet works perfectly in Excel 2002 but crashes in Excel 2003. Sorry for the long winded note but i was reading several other posts and wanted to try to give as much info so we are not reposting Q&A about the spreadsheet functions. I hope I left enough info. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solution
Hi Ben
Not sure why you cannot recreate problem but i have come up with a solution for my situation: I changed the code to the following instead of Worksheet_Calculate() to: Private Sub Worksheet_Activate() Dim x As Integer Application.ScreenUpdating = False Cells.EntireRow.Hidden = False 'unhide everything For x = 51 To 61 If Range("U" & x).Value = 0 Then Rows(x).EntireRow.Hidden = True End If Next x Application.ScreenUpdating = True End Sub So now when I change my data on Sheet 1, I then goto Sheet 5 & 7 and the rows hide when I "activate" or click on the sheet. As a side note this code also works when the sheet is protected (as long as i check allow "format rows"). I do not have to unprotect or run a sub routine to unprotect and then protect. And the code works in Excel 2002 too............... Hope this helps others. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code signing a VBA macro in 2002 and 2003 | Excel Programming | |||
Excel 2003 Issue with UsedRange.Copy (code works in Excel 2002) | Excel Programming | |||
code works in excel 2002, but not in excel 2003 | Excel Programming | |||
Excel 2002/2003 Macro Compatibility issue | Excel Programming | |||
Macro works in 2003, not in '97 | Excel Programming |