locking cells after input, or showing if input has changed

This can be done with an event macro.  Lets assume that we start with the worksheet password-protected with the password "obvious", but all the cells in column A unlocked.

Enter the following macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim A As Range

Set A = Range("A:A")

If Intersect(Target, A) Is Nothing Then Exit Sub

ActiveSheet.Unprotect Password:="obvious"

Target.Locked = True

ActiveSheet.Protect Password:="obvious"

End Sub

Once a value has been entered in a cell in column A, the macro:

• un-protects the sheet
• locks the cell
• re-protects the sheet

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.

If you are using a version of Excel later then 2003, you must save

the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

A one line addition:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim A As Range

Set A = Union(Range("B:B"), Range("E:F"), Range("Q:Q"))

If Intersect(Target, A) Is Nothing Then Exit Sub

If Target.Value = "" Then Exit Sub

ActiveSheet.Unprotect Password:="obvious"

Target.Locked = True

ActiveSheet.Protect Password:="obvious"

End Sub

Hi

This macro works great, however, when i click on the cell within a range specified in the macro, and then click off of it, the macro still locks the cell even though there is no values put into there, so is there a way which i can leave blank cells unlocked but cells that have an actual values in them locked after the value is inputed.

For example, in column A i have used the above macro and it has achieved what i want, but if i doule click a cell then click off it, without putting anything in the cell, that cell becomes locked, i dont want this so is there a code which will unlock the cell if it is blank, but when the cell as values in it, e.g. x345, it is then locked.

A single line replacement:

Just replace the Set command with:

Set A = Union(Range("B:B"), Range("E:F"), Range("Q:Q"))

thanks,

How would i make it so that several columns perform the same way,

e.g. columns b,e,f,q