Automatically protecting Sheets after saving

sumantskj

New Member
Joined
Aug 4, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Trueblue862

Board Regular
Joined
May 24, 2020
Messages
150
Office Version
  1. 365
Platform
  1. Windows
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
 

sumantskj

New Member
Joined
Aug 4, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
hey thanks ,
However I have multiple sheets and I want it to work for every sheets and not just 1 particular sheets.
 

Trueblue862

Board Regular
Joined
May 24, 2020
Messages
150
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
VBA Code:
Option Explicit
Dim Ws As Worksheet
VBA Code:
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

New Member
Joined
Aug 4, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
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

New Member
Joined
Aug 4, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
May 24, 2020
Messages
150
Office Version
  1. 365
Platform
  1. Windows
Are you entering data into the sheets directly or are you using a Userform to enter the data?
 

sumantskj

New Member
Joined
Aug 4, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi,
i am entering the data in the sheets directly.
The code is in the "thisWorkbook"
Regards
 

Trueblue862

Board Regular
Joined
May 24, 2020
Messages
150
Office Version
  1. 365
Platform
  1. Windows
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.

 

Watch MrExcel Video

Forum statistics

Threads
1,127,121
Messages
5,622,861
Members
415,935
Latest member
kes1973

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
Top