Lock/Unlock Worksheets 1

[VBASHA]

Hi,
Could someone tell me how to lock and unlock excel worksheets in VBA code?

Thanks.

 

[uberskunk]

This code was originally written for Word but I bet it would work in Excel.

We also have 2 buttons on the menus that do this for you. Might make it easy for you.

Sub ProtectForm()
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End Sub

Sub UnProtectForm()
On Error GoTo Problems
ActiveDocument.Unprotect
Exit Sub
Problems:
If Err.Number = 4605 Then
Exit Sub
Else
MsgBox Err.Description
End If
End Sub


Oh and note we have had some strange things regarding printing when a document if printed and locked.
Just something to bare in mind.

Hope this helps you,
Mike

 

[Nikita6003]

Hiya,

here's the basic code for protecting sheets in Excel:

Sub ProtectSheet()
    Dim l_wksWorkSheet As Worksheet
    
    For Each l_wksWorkSheet In ThisWorkbook
        l_wksWorkSheet.Protect "PasswordHere"
    Next l_wksWorkSheet
End Sub
Sub UnProtectSheet()
    Dim l_wksWorkSheet As Worksheet
    
    For Each l_wksWorkSheet In ThisWorkbook
        l_wksWorkSheet.Unprotect "PasswordHere"
    Next l_wksWorkSheet
End Sub

HTH


Cheers
Nikki
Look, mommy, I'm flying!

 

[shaggi]

Hi Vbasha
try recording a macro of yourself protecting worksheets, then print out the resulting code, I think this may help, as I often record macros to find the code behind them........
cheers
Shaggi

 

[Apollo6]

Nikita6003-

I just had the need arise to protect workbooks/worksheets to prevent users from deleting columns or anything else between macro runs.

The .XLA file is password protected so it doesn't matter if the password is available in the code itself.

You snippet works like a charm. Excellent!!!