Check out some similar questions!

jmherbert2099 · Sep 3, 2009, 08:09 PM

I have an spreadsheet that I am working on. I have set up Macros buttons that when selected will changed the color(format) of the cell to correspond however once the cell is changed(formatted) I want to cell to lock automatically so that it can't be changed? Is this an available option for excel?

JBeaucaire · Sep 3, 2009, 09:20 PM

To do this will require more VBA.

First, unlock the cells you want editable
- highlight all the cells
- Press Ctrl-1 to open the format window
- Click the Protection tab
- Uncheck the [ ] Locked option

Add a sheet-level macro to lock cells as they are valued

Right-click on the sheet tab and select VIEW CODE

Paste in this macro:

Code:

Option Explicit
Const pw As String = "password"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Me.Unprotect pw

    For Each cell In Target
        If cell.Value <> "" Then cell.Locked = True
    Next cell

Me.Protect pw
End Sub

Press Alt-Q to close the editor
Save your sheet

Of course, set your own protection password in that constant.

There are many many ways to do this, this just being one.