Solved: Merged Cells Null Value

[roo]

Hello,

I am somewhat of a beginner and am really struggling with this code. I read alot of forums where everyone says don't use merged cells but I'm afraid I need to. I can not seem to get the code to recognise a blank value in the merged cell. As soon as I unmerge, it works fine. Is there a work around for this or something I am missing?

This is just a simplified example of where I am coming from. Merged cells would be C4:E4
___________________________________________________

[VBA] Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then
If Range("C4") = "" Then
MsgBox ("Empty")
Else
MsgBox ("Value")
End If

End If
End Sub[/VBA]
___________________________________________________

Any help would be greatly appreciated.

Andrew

[Aussiebear]

Andrew, Welcome to Vbax. Are you sure that merged cells are the option? The advice coming through from the experts is that we should stay away from merged cells, because it causes problems. Why isn't it possible to simply select the cells and use "centre across selection"?

[Bob Phillips]

What problem are you getting. If I clear C4, it shows the Empty message. Isn't that what you want?

[roo]

Andrew, Welcome to Vbax. Are you sure that merged cells are the option? The advice coming through from the experts is that we should stay away from merged cells, because it causes problems. Why isn't it possible to simply select the cells and use "centre across selection"?

Thanks Aussiebear. Regretfully, The way the spreadsheet is laid out, the merge is needed. Am i correct in saying, if I use "centre across selection", then they can only clear the value by selecting C4, where as with a merged cell, it doesn't matter where in the range they select?

What problem are you getting. If I clear C4, it shows the Empty message. Isn't that what you want?

Did you merge cells C4:E4?

I have attached and simple example fo my error.

[Bob Phillips]

What is the error? It does everything that you put in the listr, which is exactly what you want isn't it?

Which step does other than what you expect?

[Bob Phillips]

Thanks Aussiebear. Regretfully, The way the spreadsheet is laid out, the merge is needed. Am i correct in saying, if I use "centre across selection", then they can only clear the value by selecting C4, where as with a merged cell, it doesn't matter where in the range they select?.

In merged cells they can only select the first, so if you use Center ACroos, you add a select event so that selecting any of those cells changes to selecting the first.

But ... as I said, what is the problem?

[roo]

Thanks for you quick replies.

If you use back space and enter it will msg Empty but if you use del or clear contents, nothing happens.

[Bob Phillips]

See, it helps if you tell us the full story

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells(1, 1).Address = "$C$4" Then

If Range("C4") = "" Then
MsgBox ("Empty")
Else
MsgBox ("Value")
End If

End If

End Sub
[/vba]

[roo]

I can't believe it was so simple

Sry, I just realised that backspace works.

Thanks again for you help