Forum Discussion
Lock cell without protecting worksheet
You could use VBA code to prevent some cells from being edited, but that only helps with accidental edits. Someone who knows a bit about Excel can easily circumvent it by either opening the workbook without allowing macros, or by temporarily disabling events.
Here is what you could do:
- Right-click the sheet tab.
- Select 'View Code' from the context menu.
- Copy the code listed below into the worksheet module.
- Change A2,C2,A4,C4 to the list of cells that you want to protect. You can use ranges too: A2:B10,D2:H10
- Switch back to Excel.
- Save the workbook as a macro-enabled workbook (*.xlsm).
- Instruct users to allow macros when they open the workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A2,C2,A4,C4"), Target) Is Nothing Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End Sub
- ajitjoshJan 06, 2025Copper Contributor
Dear Christina,
This works perfectly, But how to handle this when you need to copy the same set of cells to some other location say 5 lines below the present lines? Please advise.
Thanks
- HansVogelaarJan 06, 2025MVP
This discussion is about locking cells against being edited.
You appear to want something else, so please start a new discussion.
- MsKimFSep 05, 2023Copper ContributorTHANKS - i am not a programmer and have been trying to figure this out for years. You made me so happy! The advice other people are giving above does not work.
- ajitjoshMay 17, 2023Copper Contributor
Hi There, This works perfectly, I have a question, How do i make it work in a macro when i need to copy the same range of cells to a few lines below?
can you please help,
Thanks
email address removed for privacy reasons
- HansVogelaarMay 17, 2023MVP
This is a long discussion. Which code are you referring to?
- ajitjoshMay 18, 2023Copper Contributor
Hi Hans,
we are talking about the locking of cell, or a range of cells ( for a formula as an example) without protecting the sheet,
Please advise how to deal with this,
Thanks HansVogelaar
- carolinehartenMay 05, 2022Copper Contributor
HansVogelaar This worked for me! I am building a financial calculator for my job and this did the trick, thanks!
- HansVogelaarMay 05, 2022MVP
Nice to hear that!
- marina_umJan 26, 2022Copper ContributorI came by this solution through a google search, and this worked perfectly for me!
Thank you. - Christina333May 18, 2021Copper ContributorHey Hans,
Lemme try out and see.
Thank you so much for your prompt response on this. 😃