Automatically protecting Sheets after saving

[sumantskj]

Hello I am trying to write macro which automatically locks cells after user has hit save button. The sheets is protected and contains some unlocked cells where user can input data in it. Now I want those cells to get locked after user has clicked data. I have multiple sheets in the workbook but I want this to happen just for first sheets.
I tried a lot code in the forum but without any luck, could any one help me with this one.


Code:

Option Explicit

Private bRangeEdited As Boolean
Private WithEvents ws As Worksheet


Private Sub Workbook_Open()
Set ws = Range("M12:U27").Parent
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sMSG As String

sMSG = "saving the workbook will lock the cells you have entered data into." & vbLf
sMSG = sMSG & "Do you want to go ahead ?"
If Not bRangeEdited Then GoTo Xit
If Not Me.ReadOnly Then
With Range("M12:U27")
If MsgBox(sMSG, vbExclamation + vbYesNo) = vbNo Then
Cancel = True
GoTo Xit
End If
.Parent.Unprotect "1234"
If .SpecialCells(xlCellTypeBlanks).Address <> .Address Then
.SpecialCells(xlCellTypeConstants).Locked = True
bRangeEdited = False
End If
.Parent.Protect "1234"
End With
End If
Xit:
End Sub

Private Sub ws_Change(ByVal Target As Range)
If Not Intersect(Range("M12:U27"), Target) Is Nothing Then
bRangeEdited = True
End If
End Sub

 

[Trueblue862]

Insert this at the beginning of your code

with sheets(“xyz”)
.unprotect

or

.unprotect(“password“)

insert this at the end

. Protect ( with or without the password)
end with

 

[Trueblue862]

Change xyz for your sheet name

 

[sumantskj]

hey thanks ,
However I have multiple sheets and I want it to work for every sheets and not just 1 particular sheets.

 

[Trueblue862]

Here try this, paste the option explicit at the top of all your code, then paste two subs into your VBA and then call them when you need to, you can add the password in the same way as before.

Option Explicit
Dim Ws As Worksheet

Sub Unpro_SheeTS()

      For Each Ws In ActiveWorkbook.Worksheets
        Ws.Unprotect
    Next
    Exit Sub

End Sub
Sub Pro_SheeTS()
 
      For Each Ws In ActiveWorkbook.Worksheets
        Ws.Protect
    Next
    Exit Sub

End Sub

 

[sumantskj]

Option Explicit
Dim Ws As Worksheet

Private bRangeEdited As Boolean
'Private WithEvents Ws As Worksheet


Private Sub Workbook_Open()
    Set Ws = Range("A1:ZZ27").Parent
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim sMSG As String
    sMSG = "Beim Speichern der Arbeitsmappe werden die Eingabezellen gesperrt. " & vbLf
    sMSG = sMSG & "Möchten Sie fortfahren ?"
    If Not bRangeEdited Then GoTo Xit
    If Not Me.ReadOnly Then
        With Range("A1:ZZ27")
            If MsgBox(sMSG, vbExclamation + vbYesNo) = vbNo Then
                Cancel = True
                GoTo Xit
            End If
            Unpro_SheeTS
'            .Parent.Unprotect "1234"
            If .SpecialCells(xlCellTypeBlanks).Address <> .Address Then
                .SpecialCells(xlCellTypeConstants).Locked = True
                bRangeEdited = False
            End If
            Pro_SheeTS
'            .Parent.Protect "1234"
        End With
    End If
Xit:
End Sub

Private Sub ws_Change(ByVal Target As Range)
    Dim Ws As Worksheet
    If Not Intersect(Range("A1:ZZ27"), Target) Is Nothing Then
        bRangeEdited = True
    End
End Sub

Sub Unpro_SheeTS()

      For Each Ws In ActiveWorkbook.Worksheets
        Ws.Unprotect ("1234")
    Next
    Exit Sub

End Sub
Sub Pro_SheeTS()
 
      For Each Ws In ActiveWorkbook.Worksheets
        Ws.Protect ("1234")
    Next
    Exit Sub

End Sub

 

[sumantskj]

Hey tried your code however the entered cell doesn't get locked and also I believe that my Main sub doesn't get run. Something to do with Range and parent property.
I have 10 to 15 sheets and inside i HAVE CELLS which are unlocked. Those cells needs to get locked after I have entered data in it and clicked save button.
Thank you once again

 

[Trueblue862]

Are you entering data into the sheets directly or are you using a Userform to enter the data?

 

[sumantskj]

Hi,
i am entering the data in the sheets directly.
The code is in the "thisWorkbook"
Regards

 

[Trueblue862]

Ok, there are a few ways to approach this, depending on how you intend on actually using the sheets. Are the sheets single use or are they regularly updated? If they aren’t single use, how do you intend on unlocking the cells to edit them? Is there any functionality reason why you can’t simply format those particular cells to lock when the sheet is protected? It might make it easier if you could post a copy of your work sheet using the xl2bb extension for excel available on this site. Remove any sensitive information as required.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com