| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code doesn't work on merged cells
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code doesn't work on merged cells
Ian,
Doesn't the merged cell have the address of M2 Neil "IC" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code doesn't work on merged cells
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 -- Regards, Tom Ogilvy "IC" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code doesn't work on merged cells
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 "IC" wrote in message ... | 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 | | |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code doesn't work on merged cells
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? "Neil" wrote in message ... Ian, Doesn't the merged cell have the address of M2 Neil "IC" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code doesn't work on merged cells
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. "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "IC" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code doesn't work on merged cells
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 "Jim Rech" wrote in message ... 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 "IC" wrote in message ... | 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 | | |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code doesn't work on merged cells
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 wrote: 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 "Jim Rech" wrote in message ... 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 "IC" wrote in message ... | 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 | | -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code doesn't work on merged cells
Thanks for the explanation, Dave
Ian "Dave Peterson" wrote in message ... 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 wrote: 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 "Jim Rech" wrote in message ... 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 "IC" wrote in message ... | 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 | | -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "Resizing cells" code doesn't work with merged cells | Excel Discussion (Misc queries) | |||
| Autofit Merged cell Code is changing the format of my merged cells | Excel Discussion (Misc queries) | |||
| Script doesn't work when cells are merged. | Excel Discussion (Misc queries) | |||
| Autofit doesn't work with merged cells | Excel Discussion (Misc queries) | |||
| Code for autofit/merged cells doesn't work | Excel Programming | |||
![IC[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
