Lock cells on excel file after data entry and file save

Question

I am using this code provided by HansV in an answer to a question I saw within this community. This code works wonderfully to lock cells once data has been entered into them and the file is saved so the next user cannot edit any previously entered data. What I need, however, is for this to only work for my 4 weeks - (Sheet2, Sheet3, Sheet4 & Sheet 5). I am not sure how to change the worksheet range from all sheets to only the 4 that need this code to be run on.

Answer 1

Hi Suzzi-HFO,

I figured it out - It was the name of my Sheets. I renamed them in the code and it worked - THANK YOU

I am pleased that you have resolved the problem and I thank you for your kind feedback.

In order to close this thread, I would ask you kindly to mark my code as Answer.

===

Regards,

Norman

Answer 2

Hi Suzzi,

I'm Michelle and I'd be glad to help you out with your query or concern.

Microsoft provides programming examples for illustration only, without ******** either expressed or implied. This includes, but is not limited to, the implied ********** of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To try the sample macro, follow these steps:

Type the following macro code into a new module sheet.

Sub WorksheetLoop()

Dim WS_Count As Integer
Dim I As Integer

' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count

' Insert your code here.
' The following line shows how to reference a sheet within
' the loop by displaying the worksheet name in a dialog box.
MsgBox ActiveWorkbook.Worksheets(I). Name

Next I

End Sub
To run the macro, position the insertion point in the line that reads "Sub WorksheetLoop()," and press F5.

The macro will loop through the workbook and display a message box with a different worksheet name each time it runs through the loop. Note that this macro will only display

I hope this information helps you. If you have any questions, please do let me know and I'll be more than happy to assist you further with this.
If the answer is helpful, please click "Accept as Answer". Thank you and enjoy your day. :-)

Best Regards,

Michelle

Answer 3

Hi Suzzi-HFO,

I am using this code provided by HansV in an answer to a question I saw within this community. This code works wonderfully to lock cells once data has been entered into them and the file is saved so the next user cannot edit any previously entered data. What I need, however, is for this to only work for my 4 weeks - (Sheet2, Sheet3, Sheet4 & Sheet 5). I am not sure how to change the worksheet range from all sheets to only the 4 that need this code to be run on.

Try something like:

'========>>

Option Explicit

'-------->>

Private Sub Workbook_Open()

Dim wsh As Worksheet 

Dim rng As Range 

Dim arrSheets As Variant 

Dim i As Long 

Const sSheets As String = "Sheet2,Sheet3,Sheet4,Sheet5" 

arrSheets = Split(sSheets, ",") 

On Error Resume Next 

For i = LBound(arrSheets) To UBound(arrSheets) 

    Set wsh = Me.Worksheets(arrSheets(i)) 

    'For Each wsh In Me.Worksheets 

    wsh.Unprotect Password:="" 

    wsh.Cells.Locked = True 

    '        wsh.Ce11s.Locked = False 

    Set rng = wsh.UsedRange.SpecialCells(xlCellTypeConstants) 

    If Not rng Is Nothing Then 

        rng.Locked = True 

    End If 

    Set rng = wsh.UsedRange.specia1ce11s(xlCellTypeFormulas) 

    If Not rng Is Nothing Then 

        rng.Locked = True 

    End If 

    wsh.Protect Password:="" 

Next i     

End Sub

'<<========

===

Regards,

Norman

Answer 4

I replaced the code that I originally used with the code you provided above. The cells where I have entered data remained unprotected after closing/saving and reopening the file.

The code is in "This Workbook".

Answer 5

Hi Suzzi-HFO,

I replaced the code that I originally used with the code you provided above. The cells where I have entered data remained unprotected after closing/saving and reopening the file.

The code is in "This Workbook".

There was a small typo in my code!

The code should have been:

'========>>

Option Explicit

'-------->>

Private Sub Workbook_Open()

Dim wsh As Worksheet 

Dim rng As Range 

Dim arrSheets As Variant 

Dim i As Long 

Const sSheets As String = "Sheet2,Sheet3,Sheet4,Sheet5" 

arrSheets = Split(sSheets, ",") 

On Error Resume Next 

For i = LBound(arrSheets) To UBound(arrSheets) 

    Set wsh = Me.Worksheets(arrSheets(i)) 

    wsh.Unprotect Password:="" 

    wsh.Cells.Locked = False 

    Set rng = wsh.UsedRange.SpecialCells(xlCellTypeConstants) 

    If Not rng Is Nothing Then 

        rng.Locked = True 

    End If 

    **Set rng = wsh.UsedRange.SpecialCells(xlCellTypeFormulas)** 

    If Not rng Is Nothing Then 

        rng.Locked = True 

    End If 

    wsh.Protect Password:="" 

Next i 

End Sub

'<<========

The offending line, now corrected, is highlighted in red.

You may download my test file Suzzi20220707.xlsm

===

Regards,

Norman