How to protect excel workbook using VBA?

Question

with a trigger like a check box i want to protect my work book i tried excel 2003

thisworkbook.protect("password",true,true)

thisworkbook.unprotect("password")

its not working.. any suggestions

Answer 1

I agree with @Richard Morgan ... what you are doing should be working, so more information may be needed.

Microsoft has some suggestions on options to protect your Excel 2003 worksheets.

Here is a little more info ...

From help files (Protect Method):

expression.Protect(Password, Structure, Windows)

expression Required. An expression that returns a Workbook object.

Password Optional Variant. A string that specifies a case-sensitive password for the worksheet or workbook. If this argument is omitted, you can unprotect the worksheet or workbook without using a password. Otherwise, you must specify the password to unprotect the worksheet or workbook. If you forget the password, you cannot unprotect the worksheet or workbook. It's a good idea to keep a list of your passwords and their corresponding document names in a safe place.

Structure Optional Variant. True to protect the structure of the workbook (the relative position of the sheets). The default value is False.

Windows Optional Variant. True to protect the workbook windows. If this argument is omitted, the windows aren’t protected.

ActiveWorkbook.Protect Password:="password", Structure:=True, Windows:=True

If you want to work at the worksheet level, I used something similar years ago when I needed to protect/unprotect:

Sub ProtectSheet()
    ActiveSheet.Protect "password", True, True
End Sub

Sub UnProtectSheet()
    ActiveSheet.Unprotect "password"
End Sub

Sub protectAll()
    Dim myCount
    Dim i
    myCount = Application.Sheets.Count
    Sheets(1).Select
    For i = 1 To myCount
        ActiveSheet.Protect "password", true, true
        If i = myCount Then
            End
        End If
        ActiveSheet.Next.Select
    Next i
End Sub

Answer 2

Hi,

1. in your sample code you must remove the brackets, because it's not a functional assignment; also for documentary reasons I would suggest you use the := notation (see code sample below)

2. Application.Thisworkbook refers to the book containing the VBA code, not necessarily the book containing the data, so be cautious.

Express the sheet you're working on as a sheet object and pass it, together with a logical variable to the following sub:

Sub SetProtectionMode(MySheet As Worksheet, ProtectionMode As Boolean)

    If ProtectionMode Then
        MySheet.Protect DrawingObjects:=True, Contents:=True, _
                        AllowSorting:=True, AllowFiltering:=True
    Else
        MySheet.Unprotect
    End If
End Sub

Within the .Protect method you can define what you want to allow/disallow. This code block will switch protection on/off - without password in this example, you can add it as a parameter or hardcoded within the Sub. Anyway somewhere the PW will be hardcoded. If you don't want this, just call the Protection Dialog window and let the user decide what to do:

Application.Dialogs(xlDialogProtectDocument).Show

Hope that helps

Good luck - MikeD