I hope there is a simple answer I just keep overlooking, but here goes...
I am using this code in ThisWorkbook to lock specific cells upon save. It works great when I hit the 'save' button on the ribbon, it asks to lock cells, then locks them.
If I make a simple form control Save button with 'Activeworkbook.Save', it will ask to lock the cells, but does not lock them.
Any thoughts?
I am using this code in ThisWorkbook to lock specific cells upon save. It works great when I hit the 'save' button on the ribbon, it asks to lock cells, then locks them.
If I make a simple form control Save button with 'Activeworkbook.Save', it will ask to lock the cells, but does not lock them.
Any thoughts?
Code:
Option Explicit
Private bRangeEdited As Boolean
Private WithEvents ws As Worksheet
Private Sub Workbook_Open()
Set ws = Range("InputRangeItems").Parent
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sMSG As String
sMSG = "This will lock the new Inventory Items and Prices you entered, and you won't be able to change them." & vbLf
sMSG = sMSG & "Do you want to go ahead ?"
If Not bRangeEdited Then GoTo Xit
If Not Me.ReadOnly Then
With Range("InputRangeItems")
If MsgBox(sMSG, vbExclamation + vbYesNo) = vbNo Then
Cancel = True
GoTo Xit
End If
.Parent.Unprotect ""
If .SpecialCells(xlCellTypeBlanks).Address <> .Address Then
.SpecialCells(xlCellTypeConstants).Locked = True
bRangeEdited = False
End If
.Parent.Protect ""
End With
End If
Xit:
End Sub
Private Sub ws_Change(ByVal Target As Range)
If Not Intersect(Range("InputRangeItems"), Target) Is Nothing Then
bRangeEdited = True
End If
End Sub
Attachments
Last edited: