Lock cells within a locked sheet

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,254
Office Version
  1. 2016
Hi, I have a worksheet that I purposely protect and allow users to edit numerous unlocked cells within the protected sheet. However, I would like the user to be able to lock his/her own cells also. Within the protected worksheet they have access to unlocked cells. Is there a way without them un-protecting the worksheet, they can lock / prevent data edits themselves.

Thanks
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
You could use VBA

- user double-clicks cell to toggle font colour between RED and BLACK
- 2nd procedure prevents edit in cell with RED font

The toggle allows user "flexibility" - can change mind and edit if font set back to black
Select any cell or font formatting option suitable for your worksheet

Right click on sheet tab \ click View Code \ place this code goes in window on the right
Code:
Private Sub Worksheet_[COLOR=#008080]BeforeDoubleClick[/COLOR](ByVal Target As Range, Cancel As Boolean)
    With Target.Font
        If .Color = vbRed Then .Color = vbBlack Else .Color = vbRed
    End With
    Cancel = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Font.Color = vbRed Then
    Application.EnableEvents = False
        Application.Undo
    Application.EnableEvents = True
    End If
End Sub
 
Last edited:
Upvote 0
Hi, thanks for the reply, I have code already in this sheet, how would I add the code within code that exists already? Also, the only issue here, is that anyone will be able to make edits if they understand that the double click works? Is there any way around that? I understand its a tall order...

Thank you
 
Upvote 0
Do you want the user to be able to put a permanent block on edits to a cell?
- VBA could be used to lock the cell (protection auto-lifted, cell lock applied, protection auto-applied)

If not, please provide a bit more backgound


how would I add the code within code that exists already?
Post existing sheet "Event" code - I need to understand what it is doing before making suggestions
 
Upvote 0
Hi, yes I think thats correct. So, The sheet is currently password protected, the user has access to unlocked cells. However, I need some users, not all, to be able to make edits to the data in cells, and should they choose to not want anyone to be able to edit the cell, only them, then they will be able to lock the cell. However, I do not want to give the password for the protected sheet to the user. I hope that helps. If not please ask, I appreciate your help and time.
 
Upvote 0
This locks any selected cells
Code:
Sub ProtectSelectedCells()
    With ActiveSheet
        .Unprotect "password"
        selection.Locked = True
        .Protect "password", True, True
    End With
End Sub

To restrict access to a specified list of users(without making it difficult for the user)
Code:
Sub ProtectSelectedCells()
    Dim usr
    For Each usr In Array("yongle", "annie", "john")
        If Application.UserName = usr Then
            With ActiveSheet
                .Unprotect "password"
                selection.Locked = True
                .Protect "password", True, True
            End With
            Exit For
        End If
    Next usr
End Sub
You could also ask for a simple password if you wanted etc... Set your own bar.
 
Last edited:
Upvote 0
Hi,

Sorry im not great with vba but can see this looks like a great solution. Would i insert both codes into the vba area. How do i activate them, there is no double click anymore? Im sorry, as I say, not fully conversant with vba.
 
Upvote 0
The code can go in a standard module
If I were you I would keep it simple and opt for the first macro at this stage and only mention it to those who need to use it

Adding the code
In your workbook
{ALT}{F11} takes you to VBA window
In left window , look for ThisWorkbook
Right_Click on ThisWorkbook
Hover over "Insert" and select New Module
Paste either one OR the other into the window on the right (note my earlier comment)
Close VBA with the X in the top right

Giving it a shortcut
Back in worksheet
{ALT}{F8} shows the list of macros
Select ProtectSelectedCells
Click Options
Choose any letter (with or without {SHIFT} )for your short-cut
- better not to use a common one!
Test to see if it works and that it is doing what you want!

To hide the macro from general view

After above steps - go back into vba editor and nsert the word Private before Sub on the first line
Code:
Private Sub ProtectSelectedCells()
 
Last edited:
Upvote 0
Thank you so much, I have left the office now but will get back to it asap and feedback. Thank you again...
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top