Locking edited cells when saving for multiple worksheets

dazbert

New Member
Joined
Sep 3, 2013
Messages
10
Hi All, Im using the following code to lock cells Ive obviously doing something wrong as I'm trying to make this work on all the worksheets. Can anyone help or point me in the right direction

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     
If InStr(LCase(Sheets(i).Name), "dvsa") Then
        On Error Resume Next
         'Resume to next line if any error occurs
        Dim Cell As Range
        With ActiveSheet
             'first of all unprotect the entire
             'sheet and unlock all cells
            .Unprotect Password:="snails"
            .Cells.Locked = False
             'Now search for non blank cells
             'and lock them and unlock blank cells
            For Each Cell In ActiveSheet.UsedRange
                If Cell.Value = "" Then
                    Cell.Locked = False
                Else
                    Cell.Locked = True
                End If
            Next Cell
            .Protect Password:=""
             'Protect with blank password, you can change it
        End With
        Exit Sub
    End If
End Sub
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Try

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim Cell As Range, ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    With ws
         'first of all unprotect the entire
         'sheet and unlock all cells
        .Unprotect Password:="snails"
        .Cells.Locked = False
         'Now search for non blank cells
         'and lock them and unlock blank cells
        For Each Cell In .UsedRange
            If Cell.Value = "" Then
                Cell.Locked = False
            Else
                Cell.Locked = True
            End If
        Next Cell
        .Protect Password:="snails"
    End With
Next ws
End Sub
 
Upvote 0
Try

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim Cell As Range, ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    With ws
         'first of all unprotect the entire
         'sheet and unlock all cells
        .Unprotect Password:="snails"
        .Cells.Locked = False
         'Now search for non blank cells
         'and lock them and unlock blank cells
        For Each Cell In .UsedRange
            If Cell.Value = "" Then
                Cell.Locked = False
            Else
                Cell.Locked = True
            End If
        Next Cell
        .Protect Password:="snails"
    End With
Next ws
End Sub

Hi there,

Many thanks for the quick reply, much appreciated,

Ive checked this and I get runtime error '1004' unable to set the locked property of the range class
 
Upvote 0
I've just tested it with no error.

Is the password for each sheet the same - snails
 
Upvote 0

Forum statistics

Threads
1,223,151
Messages
6,170,381
Members
452,322
Latest member
CrimsonCoure

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top