Locking edited cells when saving for multiple worksheets

[dazbert]

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

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

 

[VoG]

Try

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

 

[dazbert]

Try

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

 

[VoG]

I've just tested it with no error.

Is the password for each sheet the same - snails

 

[dazbert]

I've just tested it with no error.

Is the password for each sheet the same - snails

Yes it is. Im wondering if its cos im using excel 2003?

*bangs head in fustration of companies oudated software*

 

[VoG]

There is nothing in that code that would not work in Excel 2003.

 

[dazbert]

There is nothing in that code that would not work in Excel 2003.

Okey dokey,

Any ideas why that error would come up?

or another way of doing this?

Ive probably done something stupid, just not sure what.