merged cells - code glitch

[Guest]

I have a glitch in my code. The purpose of the code is to protect the
formulas in cells on Sheet1, while still allowing a specific text entry
("Ex"). And if a user then chooses to delete the "Ex" the cell will revert
back to the original formula, by retrieving it from Sheet3.

My code works well in single cells. But I have in some merged cells (e.g.,
E5 is merged from E5 and E6), and there is a glitch in the code. In those
cells, pressing the delete key actually deletes the contents of the cells,
and results in an error. If I delete the cell by pressing 'backspace' and
then 'enter' the code works as expected.

Here is my code:
If Application.Intersect(Target, Range("E5:E49")) Is Nothing Then
Exit Sub
End If
If Target.Value <> "Ex" Then
Application.EnableEvents = False
Target.Formula = Sheets("Sheet3").Cells(Target.Row,
Target.Column).Formula
Application.EnableEvents = True
End If
Exit Sub

 

[Guest]

Merged cells are the worst plague that Microsoft has loosed on unsuspecting
users.

The best course of action is to unmerge the cells. There's almost always a
better way of doing something WITHOUT merged cells.

 

[Guest]

I considered unmerging the cells, but I'm trying to avoid it, because the
spreadsheet also serves as a printable form, and that would affect the
readability of the printout. Since this is the only problem that has arisen
from the use of the merged cells, I was hoping that I could add a line to the
code to work around it.

Maybe something like:
IF {Del key is pressed} THEN Target.Formula = ...

Would that work? I'm not sure exactly how to write it in code.
~ Horatio

 

[David Biddulph]

I doubt whether you really need merged cells for the readability of the
printoit. Have you looked at "Centre Across Selection" in Format/ Cells/
Alignment?

 

[Guest]

That's really slick. I never knew about the "Center Across Selection"
feature. Unfortunately, it is only available for horizontal format. In my
case, I would need it for vertical format.
~ Horatio

 

[Bob I]

Use Format, Cells, Alignment for that.

That's really slick. I never knew about the "Center Across Selection"
feature. Unfortunately, it is only available for horizontal format. In my
case, I would need it for vertical format.
~ Horatio


:

 

[David Biddulph]

That's what we do for horizontal, but how does one get the Centre Across
Selection option to be available within Format/ Cells/ Alignment vertically,
Bob?
[I'm using Excel 2003.]
--
David Biddulph

Use Format, Cells, Alignment for that.

....

 

[Mark Lincoln]

This is a wild guess (I'm sorry that I have no time to test it
myself): Are the cells in Sheet3 formatted the same as those in the
sheet in question? That is, are cells in Sheet3 merged the same way?
That might work or it might not, but that's the first thing I'd try.

Mark Lincoln

 

[Guest]

Yep. They are formatted and merged the same.

This is a wild guess (I'm sorry that I have no time to test it
myself): Are the cells in Sheet3 formatted the same as those in the
sheet in question? That is, are cells in Sheet3 merged the same way?
That might work or it might not, but that's the first thing I'd try.

Mark Lincoln

 

[Mark Lincoln]

Curses! Foiled again!

Okay, since the merged cells seem to be the culprit, can you unmerge
them and format them to make them appear the same (or close enough to
it)? Otherwise, you may have to change the worksheet design to
obviate the need for merged cells. This is what I do once my head
hurts too much from banging it on my desk.

As an example, I had a sheet with an identifier in the middle of three
rows and the values identified in the next three columns, using the
rows from one above that holding the idenifier to one below it:

value value
Identifier value value
value value

I formatted the three cells in the first column with a heavy outside
border and no inner borders. The printout looked the same as if I had
merged the cells.

Mark Lincoln