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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 10:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com