PDA

View Full Version : How can I have a cell lock automatically once a format is applied to it?


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.