Macro to lock all the Non Blank cells after saving the workbook.

Gentlemen,

I have been searching for a macro that would lock all the non blank cells (cells that have any value/text) after the workbook is saved.

When the user clicks on save a message box should prompt stating that "The data you entered has been locked successfully"

Thank you for your support.


Regards
Adi

try the following macro:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet, rCell As Range
For Each ws In Me
     For Each rCell In ws.UsedRange
          If rCell <> "" Then rCell.Locked = True
          Next
     Next
MsgBox "The data you entered has been locked successfully.", vbOKOnly
End Sub

This is a Workbook_BeforeSave event macro that will run every time the user saves the workbook (it runs just before the workbook is actually saved, hence the name). It must be written in "ThisWorkbook" object to function properly.

HTH!

Hello Jewel,

I have 2 questions:
1.) While saving the workbook I get a popup message to save it in .xlsm format. Is there any way where the macro can work in .xlsx format?
2.) When running the macro I am getting a 'Run-time error "438": Object doesn't support this property or method' and the following code gets highlighted yellow in macro "For Each ws In Me"
3.) Is this macro compatible with MS Excel 2010.

Appreciate your time and response. Thank you.

Adi.

Hello Jewel,

I have 2 questions:
1.) While saving the workbook I get a popup message to save it in .xlsm format. Is there any way where the macro can work in .xlsx format?
2.) When running the macro I am getting a 'Run-time error "438": Object doesn't support this property or method' and the following code gets highlighted yellow in macro "For Each ws In Me"
3.) Is this macro compatible with MS Excel 2010.

Appreciate your time and response. Thank you.

Adi.