*Resolved* Protect/Unprotect Worksheet

[TheRobster]

Hi,

I am writing an application in Excel 2000 and one of the worksheets is a "Global Settings" module in which the user specifies the conditions of a number of parameters throughout the application. Some of these options are given a value (on/off) by selecting/deselcting a Checkbox (ActiveX control).

The problem I am having is this. With some the checkboxes, the code is required to unprotect another worksheet (called "Analyse"), hide/show some rows (depending on whether the Checkbox is ticked or not) then re-protect the worksheet. This is the code that is within the Global Settings module (worksheet):

Private Sub CheckBox1_Click()

    If CheckBox1.Value = True Then

        With Sheets("Analyse")
             .Unprotect
             .Rows("55").EntireRow.Hidden = False
             .Protect
        End With
        
    Else
    
        With Sheets("Analyse")
            .Unprotect
            .Rows("55").EntireRow.Hidden = True
            .Protect
        End With
        
    End If
     
End Sub

Now here is my problem. When this code is run, it causes the Analyse worksheet to be momentarily selected as it is unprotected/protected and this causes the screen to 'flicker' between the two worksheets. The code actually works as it's supposed to, it's just that the annoying flicker looks very unprofessional.

Is there a different way to protect/unprotect the Analyse worksheet from the Global Settings worksheet, without having to actually select the Analyse sheet and causing the display to flicker?

Thanks
-Rob

*Edit*

Actually I think one solution would be if there was a way to mass-unprotect every worksheet all at the same time. Is there a way to do this? Rather than having to unprotect/protect individual worksheets at various points in my VBA code, is there a way to just say "unprotect all worksheets" at the start of my code, then say "now protect all worksheets" at the end of it?

If so it would also have to do this without having to select every worksheet (else the flicker problem would just be worse as it would momentarily select every single worksheet in turn!)

[RobDog888]

Tyry using ScreenUpdating = False and back on to True when your done.

[TheRobster]

Works a treat. Cheers RD.

-Rob