Code doesn't work on merged cells

[IC]

This code used to work when it was pointed to a single cell. I can't get it
to work now that M2:N2 are merged. Any ideas how I can get round this?
Unfortunately I need the cells merged :-(

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' If Target.Count > 1 Then Exit Sub
If Target.Address = "$M$2:$N$2" Then
If IsEmpty(Me.Range("M2:N2")) Then
Me.ComboBox1.Enabled = False
Else
Me.ComboBox1.Enabled = True
End If
End If
End Sub

Any suggestions will be gratefully received

Ian

 

[Neil]

Ian,

Doesn't the merged cell have the address of M2

Neil

 

[Tom Ogilvy]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' If Target.Count > 1 Then Exit Sub
If Target.Address = "$M$2" Then
If IsEmpty(Me.Range("M2")) Then
Me.ComboBox1.Enabled = False
Else
Me.ComboBox1.Enabled = True
End If
End If
End Sub

 

[Jim Rech]

The tricky part seems to be that when you make an entry the Target is M2 but
when you Clear the merged cell the Target is M2:N2, so:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells(1).Address = "$M$2" Then
If IsEmpty(Range("M2")) Then
ComboBox1.Enabled = False
Else
ComboBox1.Enabled = True
End If
End If
End Sub


--
Jim Rech
Excel MVP
| This code used to work when it was pointed to a single cell. I can't get
it
| to work now that M2:N2 are merged. Any ideas how I can get round this?
| Unfortunately I need the cells merged :-(
|
| Private Sub Worksheet_Change(ByVal Target As Excel.Range)
| ' If Target.Count > 1 Then Exit Sub
| If Target.Address = "$M$2:$N$2" Then
| If IsEmpty(Me.Range("M2:N2")) Then
| Me.ComboBox1.Enabled = False
| Else
| Me.ComboBox1.Enabled = True
| End If
| End If
| End Sub
|
| Any suggestions will be gratefully received
|
| Ian
|
|

 

[IC]

Yes, but it didn't appear to work on that. Looking more closely, when I
first open the sheet, ComboBox1 (CB1) is disabled. When I enter data in M2
and tab onwards, CB1 becomes enabled. Thsi is correct. If I then delete the
contents of M2, CB1 should disable, but it doesn't.

This is with the original code with just M2 in the address and range.

Any ideas?

 

[IC]

This is what I started with, but it worked by enabling the CB when M2 is
populated, but will not diable it when M2 is deleted.

 

[IC]

I'd tried combinations with and without N2, but the closest I could get was
the CB enabling when expected, but not disbling again.

Your solution works a treat, though I don't see why.

Many thanks to you and others who added their input.

Ian

 

[Dave Peterson]

If Target.Cells(1).Address

That (1) portion said to look at only the first cell in the merged area.

Without it, target.cells.address included the address of the whole merged area:

$M$2 vs. $M$2:$N$2

 

[IC]

Thanks for the explanation, Dave

Ian

If Target.Cells(1).Address

That (1) portion said to look at only the first cell in the merged area.

Without it, target.cells.address included the address of the whole merged area:

$M$2 vs. $M$2:$N$2