Script doesn't work when cells are merged.

[Guest]

Thanks for the help in advance!!!!

Looking at the script below, when cell A1 is merged with another cell, the
script doesn't work.

How do i make it work, (irregardless wether its merge or not)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Cells.Count > 1 Then Exit Sub
If Len(Target.Value) > 0 Then Exit Sub

Application.EnableEvents = False

If Target.Address = "$A$1" Then Target.Value = "This is merged cell (A1
merge to C3."

If Target.Address = "$B$1" Then Target.Value = "This is single cell"

Application.EnableEvents = True
End Sub

Thanks.

 

[Harlan Grove]

(e-mail address removed) <[email protected]>
wrote...
....

Looking at the script below, when cell A1 is merged with another cell, the
script doesn't work.

Helps if you mention how it doesn't work.

How do i make it work, (irregardless wether its merge or not)

What are you trying to do? Merged cells interact oddly with event
handlers. If you make an entry into a merged cell, the Target range
passed to the event handler is the single top-left cell of the merged
range. However, if you CLEAR a merged cell, the Target range passed to
the event handler is the entire merged range. PITA.

 

[Guest]

Hey Harlan, thanks for the advice.

How it doesn't work.?

With the script, when the delete button is hit for Cell B1, "This is single
cell" doesn't get erased. -Fixed Text after Deletion-

But when it's merged with more than one cell, somehow or rather it doesn't
work anymore.

I use it for dates, for example:

Cell A1: Dec-06 <=== hit delete

(cell A2 onwards)
Total USD Per Day
1-USDXXX (sumif(Cell A1 .....xxxxx)
2-USDXXX
3-USDXXX
4-USDXXX
5-USDXXX
6-USDXXX
7-USDXXX

1 to 31 days.

*Dec-06 (Cell A1) will be the month. And when I hit delete in cell A1 it
changes to Current Month(Jan-07) owing to date formula and the day result for
01 to 31 will change. -Fixed Text After Deletion-

This whay I can avoide the Pivot Table.

Now, I would like to use the script for Text, and sometimes the text exceeds
a single cell, like filling tax forms.

I am not proficient in VBA, I have acquired the script, but now a little
fine tuning. (Merged Cells)

And Hybrids (single and merged).

Thanks for your attention. I appreciate it.