CODEWITHSUNDEEP
excelvbams-access
cecopes
cecopes

Reputation: 85

Protecting all worksheets against change

I would like to protect worksheets in my Excel workbook pragmatically from Access so the users cannot change formulas. How to do it?

Upvotes: 1

Views: 30

Answers (1)

Vlado
Vlado

Reputation: 888

First of all unlock cells you want the user be able to edit like this:

  Cells(1, 1).Locked = False

Pass your wokrkbook to this function:

Public Sub ProtectWorksheets(workbook As excel.workbook)
    On Error GoTo ErrHandler_
    Dim ws As excel.Worksheet

    For Each ws In workbook.Worksheets

    'Protect Method with all the parameters passed in it
        ws.Protect _
            Password:="password", _
            DrawingObjects:=False, _
            Contents:=True, _
            Scenarios:=True, _
            UserInterfaceOnly:=True, _
            AllowFormattingCells:=True, _
            AllowFormattingColumns:=True, _
            AllowFormattingRows:=True, _
            AllowInsertingColumns:=True, _
            AllowInsertingRows:=True, _
            AllowInsertingHyperlinks:=True, _
            AllowDeletingColumns:=True, _
            AllowDeletingRows:=True, _
            AllowSorting:=False, _
            AllowFiltering:=False, _
            AllowUsingPivotTables:=False
    Next
ExitProc_:
    DoCmd.Hourglass False
    Exit Sub
ErrHandler_:
    'Call LogError(Err.Number, Err.Description, "ExcelUtilities", "ProtectWorksheets")
    Resume ExitProc_
    Resume ' use for debugging
End Sub

Upvotes: 1

Related Questions