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

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.

"Horatio J. Bilge, Jr." wrote:

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

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


"Duke Carey" wrote:

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.

"Horatio J. Bilge, Jr." wrote:

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

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?
--
David Biddulph

"Horatio J. Bilge, Jr." wrote in
message ...
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


"Duke Carey" wrote:

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.

"Horatio J. Bilge, Jr." wrote:

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

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" wrote:

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?
--
David Biddulph

"Horatio J. Bilge, Jr." wrote in
message ...
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


"Duke Carey" wrote:

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.

"Horatio J. Bilge, Jr." wrote:

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

Use Format, Cells, Alignment for that.

Horatio wrote:

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" wrote:


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?
--
David Biddulph

"Horatio J. Bilge, Jr." wrote in
message ...

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


"Duke Carey" wrote:


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.

"Horatio J. Bilge, Jr." wrote:


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

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

"Bob I" wrote in message
...
Use Format, Cells, Alignment for that.

Horatio wrote:

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" wrote:

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?
--
David Biddulph

"Horatio J. Bilge, Jr." wrote
in message ...

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

"Duke Carey" wrote:


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.
....

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

On Oct 9, 3:21 pm, Horatio J. Bilge, Jr.
wrote:
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

Yep. They are formatted and merged the same.


"Mark Lincoln" wrote:

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

On Oct 9, 3:21 pm, Horatio J. Bilge, Jr.
wrote:
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

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

On Oct 10, 3:39 pm, Horatio J. Bilge, Jr.
wrote:
Yep. They are formatted and merged the same.



"Mark Lincoln" wrote:
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

On Oct 9, 3:21 pm, Horatio J. Bilge, Jr.
wrote:
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- Hide quoted text -

- Show quoted text -