Select proper charts to resize
I have a spread sheet in excel that has embedded charts. Whenever the
data is changed the size of the chart can change (size). I want to run a macro that sets the charts back to a set size. Here is my code. Sub resizechart() Sheets("Analysis").Select Dim topCoor As Long, botCoor As Long, _ rgtCoor As Long, lftCoor As Long With Range("A110") topCoor = .Top lftCoor = .Left End With With Range("J125") rgtCoor = .Offset(0, 1).Left botCoor = .Offset(1, 0).Top End With With ActiveSheet.ChartObjects(1) .Top = topCoor .Left = lftCoor .Width = rgtCoor - lftCoor .Height = botCoor - topCoor End With With Range("A126") topCoor = .Top lftCoor = .Left End With With Range("J140") rgtCoor = .Offset(0, 1).Left botCoor = .Offset(1, 0).Top End With With ActiveSheet.ChartObjects(2) .Top = topCoor .Left = lftCoor .Width = rgtCoor - lftCoor .Height = botCoor - topCoor End With With Range("A141") topCoor = .Top lftCoor = .Left End With With Range("F160") rgtCoor = .Offset(0, 1).Left botCoor = .Offset(1, 0).Top End With With ActiveSheet.ChartObjects(3) .Top = topCoor .Left = lftCoor .Width = rgtCoor - lftCoor .Height = botCoor - topCoor End With With Range("G141") topCoor = .Top lftCoor = .Left End With With Range("J160") rgtCoor = .Offset(0, 1).Left botCoor = .Offset(1, 0).Top End With With ActiveSheet.ChartObjects(4) .Top = topCoor .Left = lftCoor .Width = rgtCoor - lftCoor .Height = botCoor - topCoor End With End Sub This code works to place each chart into the correct location and size on the spreadsheet. The problem is matching chartObjects(X) to Chart Name. If I select a chart in Excel and view Chart Window I see the chart window labeled as [Analysis.xls]Analysis Chart 3. Does anyone know how to determine ChartObjects(1) is equal to [Analysis.xls]Analysis Chart 3? or code to select [Analysis.xls]Analysis Chart 3 for resize? Thanks in advance for suggestions. |
All times are GMT +1. The time now is 08:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com