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:
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.