I have a spreadsheet which allows users to enter values, and then
automatically calculates various summaries from this. Before protecting the
sheet I have Locked the cells which have formulas in them, but left the
others Unlocked for data entry. This works fine until the user tries to
delete an entry they have made into an Unlocked cell, at which point they
receive an error message saying that the sheet is protected. As they are
only trying to clear an entry that they have made, I want to allow them to do
so, and can't really understand why Excel is blocking this when the cell
isn't Locked. Any suggestions? Thanks. Sue

Are you sure that this is what's happening?

Could it be that the users are clearing the cells (not clearing the contents),
then get the error when they try to add a new value to that previously unlocked
cell?

It this describes the problem, then you have a couple of choices.

#1. Teach the user to use Edit|Clear|Contents (not Edit|Clear|All) or just hit
the delete key to clear the contents of the cell.

#2. Change the normal style of your workbook so that the default cell
protection is not locked.

In xl2003 menus:
Format|style
Select Normal from the dropdown at the top
Click the modify button
Select the protection tab and uncheck Locked

Now when the users clear (completely clear--not clear contents), the cell will
return to normal--and you've made the normal protection Unlocked.

But Styles live in workbooks. You'll have to do this for each workbook that
needs it.

Sue C wrote:

I have a spreadsheet which allows users to enter values, and then
automatically calculates various summaries from this. Before protecting the
sheet I have Locked the cells which have formulas in them, but left the
others Unlocked for data entry. This works fine until the user tries to
delete an entry they have made into an Unlocked cell, at which point they
receive an error message saying that the sheet is protected. As they are
only trying to clear an entry that they have made, I want to allow them to do
so, and can't really understand why Excel is blocking this when the cell
isn't Locked. Any suggestions? Thanks. Sue

--

Dave Peterson

Yes, I haven't gone live with the spreadsheet yet, it's just me testing it,
so I'm sure it's what's happening. I can 'empty' a cell by pressing the
space bar (so, effectively overtyping my previous entry), but if I press the
Delete key then I get the sheet protection message. Since my original post
I've done some more playing around and it doesn't seem that all cells are
affected. I can't be absolutely sure, but suspect the ones that are acting
strangely are ones where cells have been merged. Is this likely to be the
cause? I'm using 2007 if that makes any difference.

"Dave Peterson" wrote:

Are you sure that this is what's happening?

Could it be that the users are clearing the cells (not clearing the contents),
then get the error when they try to add a new value to that previously unlocked
cell?

It this describes the problem, then you have a couple of choices.

#1. Teach the user to use Edit|Clear|Contents (not Edit|Clear|All) or just hit
the delete key to clear the contents of the cell.

#2. Change the normal style of your workbook so that the default cell
protection is not locked.

In xl2003 menus:
Format|style
Select Normal from the dropdown at the top
Click the modify button
Select the protection tab and uncheck Locked

Now when the users clear (completely clear--not clear contents), the cell will
return to normal--and you've made the normal protection Unlocked.

But Styles live in workbooks. You'll have to do this for each workbook that
needs it.

Sue C wrote:

I have a spreadsheet which allows users to enter values, and then
automatically calculates various summaries from this. Before protecting the
sheet I have Locked the cells which have formulas in them, but left the
others Unlocked for data entry. This works fine until the user tries to
delete an entry they have made into an Unlocked cell, at which point they
receive an error message saying that the sheet is protected. As they are
only trying to clear an entry that they have made, I want to allow them to do
so, and can't really understand why Excel is blocking this when the cell
isn't Locked. Any suggestions? Thanks. Sue

--

Dave Peterson

I've just disproved my last theory. I have some cells where data entry is
from a drop-down box, and these also cover merged cells. In these instances
I am able to clear the contents by pressing the delete key, or by using Clear
Contents after right-clicking the cell.

"Sue C" wrote:

Yes, I haven't gone live with the spreadsheet yet, it's just me testing it,
so I'm sure it's what's happening. I can 'empty' a cell by pressing the
space bar (so, effectively overtyping my previous entry), but if I press the
Delete key then I get the sheet protection message. Since my original post
I've done some more playing around and it doesn't seem that all cells are
affected. I can't be absolutely sure, but suspect the ones that are acting
strangely are ones where cells have been merged. Is this likely to be the
cause? I'm using 2007 if that makes any difference.

"Dave Peterson" wrote:

Are you sure that this is what's happening?

Could it be that the users are clearing the cells (not clearing the contents),
then get the error when they try to add a new value to that previously unlocked
cell?

It this describes the problem, then you have a couple of choices.

#1. Teach the user to use Edit|Clear|Contents (not Edit|Clear|All) or just hit
the delete key to clear the contents of the cell.

#2. Change the normal style of your workbook so that the default cell
protection is not locked.

In xl2003 menus:
Format|style
Select Normal from the dropdown at the top
Click the modify button
Select the protection tab and uncheck Locked

Now when the users clear (completely clear--not clear contents), the cell will
return to normal--and you've made the normal protection Unlocked.

But Styles live in workbooks. You'll have to do this for each workbook that
needs it.

Sue C wrote:

I have a spreadsheet which allows users to enter values, and then
automatically calculates various summaries from this. Before protecting the
sheet I have Locked the cells which have formulas in them, but left the
others Unlocked for data entry. This works fine until the user tries to
delete an entry they have made into an Unlocked cell, at which point they
receive an error message saying that the sheet is protected. As they are
only trying to clear an entry that they have made, I want to allow them to do
so, and can't really understand why Excel is blocking this when the cell
isn't Locked. Any suggestions? Thanks. Sue

--

Dave Peterson

First, I wouldn't use the spacebar to clear a cell. The cell would actually
contain a character (or lots of characters).

Second, I'm not sure about the merged cells--but maybe you could unprotect the
worksheet. And unlock the merged cells. Then reprotect the sheet and test.

If that doesn't work, the next test is to unprotect the worksheet, unmerge the
cells, unlock all the cells in the mergearea, merge those cells, and reportect
the sheet and test.

I try my best to stay away from merged cells!

========
Actually, the first thing I would test is to make sure that there is no macro
running (event or anything else).

I'd close excel and restart it in safe mode:

Close excel
windows start button|Run
excel /safe

then file|open the workbook and test.

You could have an event macro that's firing -- or you may have a macro that's
taken over the delete key????

And since this test is easier that fiddling with all those merged cells, sheet
protection, ..., I'd try it first.

Sue C wrote:

Yes, I haven't gone live with the spreadsheet yet, it's just me testing it,
so I'm sure it's what's happening. I can 'empty' a cell by pressing the
space bar (so, effectively overtyping my previous entry), but if I press the
Delete key then I get the sheet protection message. Since my original post
I've done some more playing around and it doesn't seem that all cells are
affected. I can't be absolutely sure, but suspect the ones that are acting
strangely are ones where cells have been merged. Is this likely to be the
cause? I'm using 2007 if that makes any difference.

"Dave Peterson" wrote:

Are you sure that this is what's happening?

Could it be that the users are clearing the cells (not clearing the contents),
then get the error when they try to add a new value to that previously unlocked
cell?

It this describes the problem, then you have a couple of choices.

#1. Teach the user to use Edit|Clear|Contents (not Edit|Clear|All) or just hit
the delete key to clear the contents of the cell.

#2. Change the normal style of your workbook so that the default cell
protection is not locked.

In xl2003 menus:
Format|style
Select Normal from the dropdown at the top
Click the modify button
Select the protection tab and uncheck Locked

Now when the users clear (completely clear--not clear contents), the cell will
return to normal--and you've made the normal protection Unlocked.

But Styles live in workbooks. You'll have to do this for each workbook that
needs it.

Sue C wrote:

I have a spreadsheet which allows users to enter values, and then
automatically calculates various summaries from this. Before protecting the
sheet I have Locked the cells which have formulas in them, but left the
others Unlocked for data entry. This works fine until the user tries to
delete an entry they have made into an Unlocked cell, at which point they
receive an error message saying that the sheet is protected. As they are
only trying to clear an entry that they have made, I want to allow them to do
so, and can't really understand why Excel is blocking this when the cell
isn't Locked. Any suggestions? Thanks. Sue

--

Dave Peterson


--

Dave Peterson

I think we are getting somewhere (by which I mean you are getting somewhere,
and if only I understood where)!

Macros are completely outside of my range of knowledge, however, yesterday I
copied one from this forum, which was designed to allow text wrapping and row
height auto-adjusting in Merged cells.

By going into my spreadsheet in Safe Mode my problem with deleting cell
entries disappears, so I presume the problem is in someway connected to the
macro. But as it may as well be written in Greek to me, I'm hoping you can
spot the error. I've copied the contents below!

Thanks for your help. Sue

PS. And yes, I now fully realise that Merged Cells Are the Root of All Evil!

Macro Content:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range


With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub





"Dave Peterson" wrote:

First, I wouldn't use the spacebar to clear a cell. The cell would actually
contain a character (or lots of characters).

Second, I'm not sure about the merged cells--but maybe you could unprotect the
worksheet. And unlock the merged cells. Then reprotect the sheet and test.

If that doesn't work, the next test is to unprotect the worksheet, unmerge the
cells, unlock all the cells in the mergearea, merge those cells, and reportect
the sheet and test.

I try my best to stay away from merged cells!

========
Actually, the first thing I would test is to make sure that there is no macro
running (event or anything else).

I'd close excel and restart it in safe mode:

Close excel
windows start button|Run
excel /safe

then file|open the workbook and test.

You could have an event macro that's firing -- or you may have a macro that's
taken over the delete key????

And since this test is easier that fiddling with all those merged cells, sheet
protection, ..., I'd try it first.

Sue C wrote:

Yes, I haven't gone live with the spreadsheet yet, it's just me testing it,
so I'm sure it's what's happening. I can 'empty' a cell by pressing the
space bar (so, effectively overtyping my previous entry), but if I press the
Delete key then I get the sheet protection message. Since my original post
I've done some more playing around and it doesn't seem that all cells are
affected. I can't be absolutely sure, but suspect the ones that are acting
strangely are ones where cells have been merged. Is this likely to be the
cause? I'm using 2007 if that makes any difference.

"Dave Peterson" wrote:

Are you sure that this is what's happening?

Could it be that the users are clearing the cells (not clearing the contents),
then get the error when they try to add a new value to that previously unlocked
cell?

It this describes the problem, then you have a couple of choices.

#1. Teach the user to use Edit|Clear|Contents (not Edit|Clear|All) or just hit
the delete key to clear the contents of the cell.

#2. Change the normal style of your workbook so that the default cell
protection is not locked.

In xl2003 menus:
Format|style
Select Normal from the dropdown at the top
Click the modify button
Select the protection tab and uncheck Locked

Now when the users clear (completely clear--not clear contents), the cell will
return to normal--and you've made the normal protection Unlocked.

But Styles live in workbooks. You'll have to do this for each workbook that
needs it.

Sue C wrote:

I have a spreadsheet which allows users to enter values, and then
automatically calculates various summaries from this. Before protecting the
sheet I have Locked the cells which have formulas in them, but left the
others Unlocked for data entry. This works fine until the user tries to
delete an entry they have made into an Unlocked cell, at which point they
receive an error message saying that the sheet is protected. As they are
only trying to clear an entry that they have made, I want to allow them to do
so, and can't really understand why Excel is blocking this when the cell
isn't Locked. Any suggestions? Thanks. Sue

--

Dave Peterson


--

Dave Peterson

I don't see anything that would cause trouble in the code.

But I did have to protect the worksheet and allow the user to:
format cells, format columns and format rows.

But after I did that, the code worked ok.

But that was with just minor testing (and in xl2003).

Maybe you could describe the range that was merged,
each of the cell's (in the mergearea) "lockedness" property,
and the options that you chose when you protected the worksheet.





Sue C wrote:

I think we are getting somewhere (by which I mean you are getting somewhere,
and if only I understood where)!

Macros are completely outside of my range of knowledge, however, yesterday I
copied one from this forum, which was designed to allow text wrapping and row
height auto-adjusting in Merged cells.

By going into my spreadsheet in Safe Mode my problem with deleting cell
entries disappears, so I presume the problem is in someway connected to the
macro. But as it may as well be written in Greek to me, I'm hoping you can
spot the error. I've copied the contents below!

Thanks for your help. Sue

PS. And yes, I now fully realise that Merged Cells Are the Root of All Evil!

Macro Content:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

"Dave Peterson" wrote:

First, I wouldn't use the spacebar to clear a cell. The cell would actually
contain a character (or lots of characters).

Second, I'm not sure about the merged cells--but maybe you could unprotect the
worksheet. And unlock the merged cells. Then reprotect the sheet and test.

If that doesn't work, the next test is to unprotect the worksheet, unmerge the
cells, unlock all the cells in the mergearea, merge those cells, and reportect
the sheet and test.

I try my best to stay away from merged cells!

========
Actually, the first thing I would test is to make sure that there is no macro
running (event or anything else).

I'd close excel and restart it in safe mode:

Close excel
windows start button|Run
excel /safe

then file|open the workbook and test.

You could have an event macro that's firing -- or you may have a macro that's
taken over the delete key????

And since this test is easier that fiddling with all those merged cells, sheet
protection, ..., I'd try it first.

Sue C wrote:

Yes, I haven't gone live with the spreadsheet yet, it's just me testing it,
so I'm sure it's what's happening. I can 'empty' a cell by pressing the
space bar (so, effectively overtyping my previous entry), but if I press the
Delete key then I get the sheet protection message. Since my original post
I've done some more playing around and it doesn't seem that all cells are
affected. I can't be absolutely sure, but suspect the ones that are acting
strangely are ones where cells have been merged. Is this likely to be the
cause? I'm using 2007 if that makes any difference.

"Dave Peterson" wrote:

Are you sure that this is what's happening?

Could it be that the users are clearing the cells (not clearing the contents),
then get the error when they try to add a new value to that previously unlocked
cell?

It this describes the problem, then you have a couple of choices.

#1. Teach the user to use Edit|Clear|Contents (not Edit|Clear|All) or just hit
the delete key to clear the contents of the cell.

#2. Change the normal style of your workbook so that the default cell
protection is not locked.

In xl2003 menus:
Format|style
Select Normal from the dropdown at the top
Click the modify button
Select the protection tab and uncheck Locked

Now when the users clear (completely clear--not clear contents), the cell will
return to normal--and you've made the normal protection Unlocked.

But Styles live in workbooks. You'll have to do this for each workbook that
needs it.

Sue C wrote:

I have a spreadsheet which allows users to enter values, and then
automatically calculates various summaries from this. Before protecting the
sheet I have Locked the cells which have formulas in them, but left the
others Unlocked for data entry. This works fine until the user tries to
delete an entry they have made into an Unlocked cell, at which point they
receive an error message saying that the sheet is protected. As they are
only trying to clear an entry that they have made, I want to allow them to do
so, and can't really understand why Excel is blocking this when the cell
isn't Locked. Any suggestions? Thanks. Sue

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson