'Save' macro vs. 'save' button not working ?

[Rodger]

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?

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

 

[NARAYANK991]

Hi ,

See the file now.

Idea is Jindon's here :

http://www.ozgrid.com/forum/showthread.php?t=179438

Narayan

 

[Rodger]

Ahhh, that is fantastic ! Thank you to both of you.
I would have never figured that out, and didnt know where else to search before asking you.
Cheers !!!