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
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