Locking cells without protecting worksheet?

pscseti said:

Hi I'm trying to lock a single cell without setting its cell format to keep it locked with all other cells on the worksheet unlocked and then using tools to protect the worksheet. The reason I want to do this without protecting the worksheet is because I'm using the group function on some of my rows. I have found that when a worksheet is protected even if all cell format do not have the locked box checked it will not allow me to use the ungroup because of the protection.

In short is there a way to prevent a cells content from being changed without using the cell format lock function?

Click to expand...

Hi pscseti:

We can accomplish what you want by using a Worksheet_selectionChange event. Assuming you want cell C3 not to be changed, then ...

Please post back if it works for you -- otherwise explain a little further and let us take it from there.

Help with VBA

Sorry I don't know anything about VBA. Where do I type this code. Do I use a drop down menu from within excel?...is there anyway you can easily describe how I go about this?

Thanks

Don't want to butt in, Yogi, but I don't think you're on the Board.

Pscseti, right click on the sheet tab, and select View Code. Paste it in there. I would add

Range("C2").Select

after the MsgBox.

Richard

still not working

Thanks Richard & Yogi,

I tried putting the code in as you indicated, but the message appears whereever I type on the sheet and I'm able to type in any cell.

I'm trying to prevent changes to cell A1.

Any further modifications/additions to code you can suggest.

Again, thanks very much.

Try replacing that code with this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
MsgBox "Sorry, cell " & Target.Address & " is off limits.", 16, "Hey, leave me alone!!"
End Sub


Remember, to put it where it needs to go, right click on your sheet tab, left click on View Code, paste the above code into the large white area that is the worksheet module (replacing the code you don't want), and press Alt+Q to return to the worksheet.

A few caveats - - This will not protect against formatting or spell check changes. Also, absent more code, it's possible to select the cell along with a range of other cells, and as long as A1 is not the active cell, someone can hit the delete key and not trigger the Change event. You should know that if you did ever want to lock the cell and use sheet protection, you'd be better off than this code can provide.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then Target.Offset(1, 0).Select
End Sub

This code causes the cell selected to change when A1 is clicked... there fore cell change cannot occur.

RichardS said:

Don't want to butt in, Yogi, but I don't think you're on the Board.

Pscseti, right click on the sheet tab, and select View Code. Paste it in there. I would add

Range("C2").Select

after the MsgBox.

Richard

Click to expand...

Hi Richard:

THANKS for covering for me!