Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97/2003 VBA merged cells
Hello
I have a problem which appears to be to do with the different ways in which Excel 97 and 2003 treat merged cells. In an Excel 2003 worksheet cell G5 is merged from 4 cells (G5:J5) The following Excel 2003 VBA code works well ("OPS" is a named range for cell G5): Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Cells.Count = 1 Then 'do nothing if more than one cell is changed If Target.Address = Range("OPS").Address Then 'do stuff 'rest of code cut End If End If End Sub The code fails in Excel 97. Stepping through the code reveals that Target.Cells.Count is equal to 4 and likewise Target.Address is equal to "$G$5:$J$5". Is there anything I can do about this other than to check which version of Excel is running? Any other coding suggestions welcome. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97/2003 VBA merged cells
Maybe something like this untested pseudo code. You will need to test an
approach like this to see what works the way you want. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Cells.Count < 1 Then 'do nothing if more than one cell is if Target(1).MergeArea.Address < Target.Address then exit sub else set rng = Target(1) end if Else set rng = Target End if If rng.Address = Range("OPS").Address Then 'do stuff 'rest of code cut End If End If -- Regards, Tom Ogilvy "Tony James" wrote: Hello I have a problem which appears to be to do with the different ways in which Excel 97 and 2003 treat merged cells. In an Excel 2003 worksheet cell G5 is merged from 4 cells (G5:J5) The following Excel 2003 VBA code works well ("OPS" is a named range for cell G5): Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Cells.Count = 1 Then 'do nothing if more than one cell is changed If Target.Address = Range("OPS").Address Then 'do stuff 'rest of code cut End If End If End Sub The code fails in Excel 97. Stepping through the code reveals that Target.Cells.Count is equal to 4 and likewise Target.Address is equal to "$G$5:$J$5". Is there anything I can do about this other than to check which version of Excel is running? Any other coding suggestions welcome. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97/2003 VBA merged cells
Try -
If Target.Count = Target(1).MergeArea.Count Then Regards, Peter T "Tony James" wrote in message ups.com... Hello I have a problem which appears to be to do with the different ways in which Excel 97 and 2003 treat merged cells. In an Excel 2003 worksheet cell G5 is merged from 4 cells (G5:J5) The following Excel 2003 VBA code works well ("OPS" is a named range for cell G5): Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Cells.Count = 1 Then 'do nothing if more than one cell is changed If Target.Address = Range("OPS").Address Then 'do stuff 'rest of code cut End If End If End Sub The code fails in Excel 97. Stepping through the code reveals that Target.Cells.Count is equal to 4 and likewise Target.Address is equal to "$G$5:$J$5". Is there anything I can do about this other than to check which version of Excel is running? Any other coding suggestions welcome. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97/2003 VBA merged cells
Tom and Peter, many thanks for your suggestions. I'll try your code
when I return to work tomorrow, they both look better than coding for different Excel versions. Thanks Tony |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Numbering a merged Cells Excel 2003 | Excel Discussion (Misc queries) | |||
How can I sort an Excel Doc containing merged & non-merged cells? | Excel Discussion (Misc queries) | |||
2003: Autofit merged cells | Excel Discussion (Misc queries) | |||
Autofit Merged cell Code is changing the format of my merged cells | Excel Discussion (Misc queries) | |||
(2003) Pasting into merged cells | Excel Worksheet Functions |