Lock/Unlock cell on selection vba.

[Lanny]

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!!!

 

[mikerickson]

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"?

 

[Lanny]

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.

 

[mikerickson]

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.

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.

 

[Lanny]

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!