Member Avatar for Luma

Hi All!! Plse HELP!!

I need to avail various ranges on different sheets in an in Excel 2003 workbook for users to type in. However, I need the cells to be locked upon saving of workbook. How do I do it?

Thanks
Luma

  • 2 Contributors
  • 2 Replies
  • 121 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Luma

Dani AI

Generated

— following up on 's pointer about Tools->Protection: Excel's Locked flag only matters when the worksheet is protected. Two practical VBA patterns that meet your goal are below. Both assume you mark the cells users should be able to fill by clearing Format Cells -> Protection -> Locked (so only those start as editable).

Option A — lock every non-empty unlocked cell when the workbook is saved (simple, no per-change tracking). Put this in the ThisWorkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim ws As Worksheet
    Dim rng As Range, c As Range
    Dim psw As String

    psw = ""  ' set a password here if desired

    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next
        ws.Unprotect Password:=psw
        Set rng = ws.UsedRange.SpecialCells(xlCellTypeConstants)
        On Error GoTo 0

        If Not rng Is Nothing Then
            For Each c In rng.Cells
                If Not c.Locked Then c.Locked = True
            Next c
        End If

        ws.Protect Password:=psw
    Next ws
    Application.ScreenUpdating = True
End Sub

Option B — track changed ranges as users edit and lock only those specific ranges on save (more precise). Add a standard module with a public collection, initialize it on open, add a small Worksheet_Change to each sheet you want tracked, and then process that list in Workbook_BeforeSave. This keeps locks limited to the actual edited cells rather than every non-empty cell.

Notes and cautions

  • Macros must be enabled for this to work. Test on a copy first.
  • Storing a password in code is not secure; leave blank to protect without a password or prompt for a password instead.
  • If sheets are already protected at design time, unprotect them in code before changing .Locked. The examples above unprotect/protect automatically.
  • SpecialCells can error if no constants exist—examples handle that with error trapping.
  • For very large workbooks or many edits, Option B scales better and avoids locking stray labels.

Recommended Answers

All 2 Replies

Member Avatar for joshSCH

Tools-protection gives you 'locking' options.. is this what you mean?

Member Avatar for Luma

Hi joshSCH,

No. What I mean is that I need a script/code than runs "on change" locking only the changed cells. In other words, the user should be able to complete certain ranges, and upon saving, the changed (completed) cells should be locked, preventing future editting.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.