Lock cells within a locked sheet

[nparsons75]

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

 

[Yongle]

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

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

 

[nparsons75]

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

 

[Yongle]

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

 

[nparsons75]

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.

 

[Yongle]

This locks any selected cells

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)

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.

 

[nparsons75]

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.

 

[Yongle]

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

Private Sub ProtectSelectedCells()

 

[nparsons75]

Thank you so much, I have left the office now but will get back to it asap and feedback. Thank you again...