Stopped flicker, added protection and it started again HELP

[RobbieNZ]

Hi,

I have this macro which hides rows based on the results in other cells.
All works fine, had to add Application.ScreenUpdating = False/ and True to stop the page flickering each time a validation box was selected BUT then i went to complete the project by protecting the sheets and two things happend. First I got a
Runtime error 1004

I looked around the internet to find a solution to the error so added an umprotect command to the start and a protect command to the end of the macro so it would use my protection password to unlock and relock the page after running the macro. Unfortunately now I have the flicker back and can't understand why. Can someone please help me with this. It is the last thing I need to do to finish the project )

I have removed the protect and unprotect portion of the macro at this stage because at least my macro runs at the moment but I really do need to protect the sheets before I can give it to anyone to use.

Private Sub Worksheet_Calculate()
Dim myresult3 As String
Dim myresult4 As String
 
Application.ScreenUpdating = False
Application.EnableEvents = False
 
Rows("1:" & Worksheets("PUMP CONTROL").UsedRange.Rows.Count).EntireRow.Hidden = False
 
 
myresult3 = Worksheets("PUMP CONTROL").Cells(22, 1).Value 'FGC options
myresult4 = Worksheets("PUMP CONTROL").Cells(10, 1).Value 'FGC options
 
Select Case myresult4
Case "FGC"
Rows("10:11").EntireRow.Hidden = True
Rows("23:23").EntireRow.Hidden = True
End Select
 
Select Case myresult3
 
Case "", "None", "0", "APP"
Rows("21:25").EntireRow.Hidden = True
Rows("47:51").EntireRow.Hidden = True
 
End Select
Application.EnableEvents = True
Application.ScreenUpdating = True
 
End Sub

 

[craig.penny]

If you run the code you posted what happens? Flicker or no?

Could you post the code with the protect and unprotect lines? I'll try to run it and see what happens.

 

[RobbieNZ]

Hi sorry for the delayed reply. Had a couple of days off work )

Ok, minimial flicker without the protect (I can live with it)
Much more flicker when I add the protect in as below.


Give it a go, let me know what you think.
Thanks heaps!

Private Sub Worksheet_Calculate()
Dim myresult3 As String
Dim myresult4 As String

Application.ScreenUpdating = False
Application.EnableEvents = False
[B][COLOR=red]ActiveSheet.Unprotect Password:="Bruce"[/COLOR][/B]

 
Rows("1:" & Worksheets("PUMP CONTROL").UsedRange.Rows.Count).EntireRow.Hidden = False


myresult3 = Worksheets("PUMP CONTROL").Cells(22, 1).Value 'FGC options
myresult4 = Worksheets("PUMP CONTROL").Cells(10, 1).Value 'FGC options

Select Case myresult4
Case "FGC"
Rows("10:11").EntireRow.Hidden = True
Rows("23:23").EntireRow.Hidden = True
End Select
 
Select Case myresult3
Case "", "None", "0", "APP"
Rows("21:25").EntireRow.Hidden = True
Rows("47:51").EntireRow.Hidden = True

End Select
[B][COLOR=red]ActiveSheet.Protect Password:="Bruce"[/COLOR][/B]
Application.EnableEvents = True
Application.ScreenUpdating = True
 
End Sub

 

[craig.penny]

I can't duplicate the flickering. Not even when I set ScreenUpdating and EnableEvents to true instead of false at the beginning. I think hiding and unhiding rows has caused me problems before as far as slowing things down and making the screen flicker but I just can't make it happen with this code. The only thing I can think of (and I'm sure it's a long shot) is do you have any other event-fired code that would get set off each time a row is hidden or unhidden? It could be at the worksheet or workbook level or possibly in a class module. Is there anything else you think might be relevant?

 

[RobbieNZ]

Hi,
I deleted a bunch of macros that were before my time but made no difference, still the same flicker.

I have another sheet in the workbook using the same format of macro but activating on different cells. Could having two similar macros within two different sheets cause the problem?
Wait, Will cut one out amd see if i still get the same issue
Cheers,
R.

 

[RobbieNZ]

Oh darnit!, that IS what is causing the problem. I cut this second macro out of the other sheet and the flicker in the first ceased. Any idea what I can do to fix this?

Here is the code from the second sheet

Private Sub Worksheet_Calculate()
Dim myresult As String
Dim MyResult1 As String
Dim MyResult2 As String
Application.ScreenUpdating = False
Application.EnableEvents = False
 
Rows("1:" & Worksheets("Q U O T E").UsedRange.Rows.Count).EntireRow.Hidden = False
myresult = Worksheets("Q U O T E").Cells(99, 1).Value 'Controls'
MyResult1 = Worksheets("Q U O T E").Cells(64, 1).Value 'Valve Chamber'

''''''''''''''''''''''''''''''''''''''''''''''''''
Select Case MyResult1
Case "", "None", "0"
Rows("64:83").EntireRow.Hidden = True
Rows("157:157").EntireRow.Hidden = True
End Select
Select Case myresult
Case "", "None", "0"
Rows("99:114").EntireRow.Hidden = True
End Select
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[craig.penny]

Sorry for the delay. I will look at this a little later today.

 

[craig.penny]

If there is some formula on one of those sheets that references the other then perhaps when you calculate it is causing both sheets to calculate thereby calling each Worksheet_Calculate over and over. Is that possible?

 

[RobbieNZ]

Hi Craig,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Yes one references in to the other. I didn't realise that would trigger the worksheet calculate over and over but it makes perfect sense now that you say it. <o></o>
<o></o>
Leave it with me. I have deleted the macro from the second sheet (the sheet which references the first sheet), <o></o>
I have changed how the macro works in the second sheet and added a control button so it will no longer automatically calculate. Fingers crossed this solves it.<o></o>
<o></o>
Thanks so much for the help.<o></o>
Best,<o></o>
R.<o></o>

 

[craig.penny]

Let me know if it works out.

Good luck!