Lock Cells after Saving and Requiring a Password when trying to alter values next time

[Fahad Feroz]

I want to protect some cells in such a way that once a user saves the file it would be password locked. If the data is attempted to be manipulated after the 1st saving, it will require a password to access the locked cells. I know it is only possible in Macro.

I want to apply the above protection in the yellow marked cells. I am sort of a rookie in VBA. Would you be kind enough to share the code so that I can do the above.

Thanks in advance!!

 

[Stevie]

Hi Fahad
see oif you can put together these snippets:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
        Cancel as Boolean)
    a = MsgBox("Do you really want to save the workbook?", vbYesNo)
    If a = vbNo Then Cancel = True
'enter your protection code here
End Sub

Sub CellLocker()
Cells.Select
' unlock all the cells
Selection.Locked = false
' enter your ranges to lock as below
Range("A1").Select
' lock those cells
Selection.Locked = true
' now we need to protect the sheet to restrict access to the cells.
' I protected only the contents you can add whatever you want
ActiveSheet.Protect DrawingObjects:=false, Contents:=true, Scenarios:=false
End Sub

https://msdn.microsoft.com/en-us/library/office/ff840057.aspx
http://stackoverflow.com/questions/7763205/lock-certain-cells-in-a-range

 

[Fahad Feroz]

Thanks for your quick reply and response.

But how to joint the two codes!

Man, I admit that I am a rookie hence, would highly appreciate if you kindly apply your VBA formula on my sample worksheet so that I can really understand how to write a code in such circumstance.

 

[Fahad Feroz]

Just to let you know .. I did try to combine your codes like this (I uploaded a file on this). I fell like an A** as I am sure you are gonna laugh at the work :-(

 

[Stevie]

Fahad, I commented IN the code to tell you what to do.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel as Boolean)
Cells.Select
Selection.Locked = false
Range("A1").Select
Selection.Locked = true
Range("A2").Select
Selection.Locked = true
Range("A3").Select
Selection.Locked = true
Range("A4").Select
Selection.Locked = true
ActiveSheet.Protect
End Sub

Change the cell references to what you need them to be.

 

[Fahad Feroz]

Thanks for the quick reply.

However the formula did not do anything. Please have a look a the attached file to the see what went wrong.

 

[Stevie]

Hi Fahad,
please move the code to the 'ThisWorkbook' module and try again.
I have tested and it works fine for me.