Excel - Locking a Sheet

Posted on 2016-07-26
Medium Priority
Last Modified: 2016-07-26
I have a Excel workbook with multiple sheets.   Each sheet equals a month.

Many cells on a sheet are locked and some are unlocked and editable.

Each sheet has some cells pulling dynamic info from cells on a master sheet.

I'd like a button that at the end of the month i can click to lock the particular sheet from further editing including further changes in dynamic cells (lock the current dynamic values and stop changing based on the master sheet).    

Thanks for any input.
Question by:tailoreddigital
  • 4
  • 2

Accepted Solution

Simulog earned 2000 total points
ID: 41730248
Add a module, paste the following code into it and link a button on the worksheet to the macro
Option Explicit

Sub MakeStatic()
' This copies the used area and pastes values, turns on the locking of all used cells
' and locks the worksheet (without a password)
' Jörgen Möller 26Jul 2016
    With ActiveSheet.Range("A1", ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell))
        .PasteSpecial Paste:=xlPasteValues
        .Locked = True
    End With
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Open in new window

It should do the trick.

Good Luck
LVL 23

Author Comment

ID: 41730255
I'm testing this now.    

I know somebody is going to lock it and then realize that they need to change something.  

What code could i use to make it editable after MakeStatic?   It only would need to be editable, the dynamic cells don't need to revert to dynamic.
LVL 23

Author Comment

ID: 41730289
MakeStatic works.   I appreciate your expertise.    

To accomplish my need above, It looks like i'd just need to create another script that simply unprotects the active sheet.
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

LVL 23

Author Closing Comment

ID: 41730314
Thanks for the help

Expert Comment

ID: 41730315
Yes and that is simple
Option Explicit

Sub UnProtSheet()
End Sub

Open in new window

LVL 23

Author Comment

ID: 41730358
Thanks again, have a great day

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Read this tutorial to learn how to fix repeating password error prompts when setting up Gmail IMAP with Microsoft Outlook. The entire process is described with step by step, illustrated instructions. Enjoy...
With the emergence of Office 365 as a superior email communication platform, many organizations have started switching over to it.  After migrating to Office 365, sometimes users, as well as organizations, will have to import PST files to Office 36…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question