Lock/Unlock cell on selection vba.

Lanny

New Member
Joined
Oct 12, 2018
Messages
4
I need a macro that will unlock (an already locked cell) when selected and then lock back when deselected.

Is this possible?

Thanks in advance!!!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Locking and unlocking will do nothing unless the sheet is protected.
Un protecting and re-protecting the sheet is possible (if you know the password), but raises the question "why bother protecting"?
 
Upvote 0
Locking and unlocking will do nothing unless the sheet is protected.
Un protecting and re-protecting the sheet is possible (if you know the password), but raises the question "why bother protecting"?

I have the password setup and know how to unprotect and protect. The Excel sheet is setup so it can be edited without a macro, but to initially get it started you will need the macro. The whole workbook changes based on the value of a range of cells, which is why I need them locked unless macros are enabled.
 
Upvote 0
Why not just unlock the cells that the user can change. In any case, if you put this in the code module for the sheet in question, it should do what you want.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Me
        .Unprotect
        .Cells.Locked = True
        If Target.Cells.Count = 1 Then
            Target.Locked = False
        End If
        .Protect
    End With
End Sub
Note that if you select a multi-cell region, they will remain locked.
 
Upvote 0
Thanks for the reply. This is what I'm trying to achieve (which I don't think the code posted will do what I'm trying to do).

When Excel is started one cell is locked, this cell changes the rest of the sheet based on it's value using vba, so I'm trying to ensure that it cannot be edited until macros are enabled.

I'm trying to keep the cell locked unless vba is enabled, but I need a way to lock the cell. Using the before save vba is not an option in this particular case.

So, I'm trying to allow the sheet to let one cell be locked until vba is activated, and remains locked until the cell is actually edited.

Ideally the cell is locked, when it is clicked on it unlocks, then when clicked out it locks back. I'm running into a wall trying to figure this out lol.

Thanks for any help or suggestions!!

Much appreciated.

Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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