Code does not work on merged cells

[fuzzyfreak]

The following code works fine on single cells but I also have Dat
Validation which for cosmetic purposes merges to the next cell e.g
Cell B3 merges into cell C3 - why, if I use either B3 or B3:C3 in th
code below does it not work? i.e. I should get an error message if
try to delete data from one of the cells.

Thanks

_Code_
Application.EnableEvents = False

If (Len(Range("b10")) = 0) Or (Len(Range("c10")) = 0) O
(Len(Range("f10")) = 0) Then
With Target
If .Value = "" Then
Application.EnableEvents = False
.Value = "Invalid"
MsgBox "You have an invalid entry, please try again."
.Select
SendKeys "%{Down}"
End If
End With
End If
Application.EnableEvents = Tru

 

[Myrna Larson]

I don't understand the connection between Data/Validation and merged cells...

IMO, you should avoid merged cells like the plague. You've just pointed out
another of the many reasons for that opinion.

If all of these merges are a cell on the left with one or more cells to its
immediate right (i.e. in the same row), and the point is to center some text
across, for example, B3:C3, you can achieve that result by selecting both
cells, then setting the horizontal alignment to "center across selection".

 

[Gord Dibben]

I imagine the point of having merged cells is to allow a wider selection box
for the DV list.

A good idea until you try to manipulate the merged cells.

Check out Help on "MergeCells Property"

May be an example there that could assist with code-writing.


Gord Dibben Excel MVP