Positioning Multiple Charts on a WorkSheet by cell position rather than pixels

[frank_AL]

I have code that creates multiple charts on a single worksheet. The number of charts varies based upon filter criteria applied by the user. I added code to apply page breaks and it works fine on my PC and with my printer. However, when testing the code last week at the customer's premise, I encountered problems with the page breaks due to different printer type. The code will be used by about 30 Account Managers and they will have many different printer types.

I am trying to adjust the chart size and location based upon cell values once the charts have been created.
I am trying to use variables for the chart object, the worksheet and the cells to manage the fact the number of charts varies.
Here is the code I am attempting to use. I found the basics for this code in another MrExcel post and tried to modify it to use multiple variables.
I realize (being a relative VBA novice that I could possibly accomplish when the charts are created using .Location but I am not familiar with how to use that feature.

' Adjust Chart Position by Row rather that Pixel to support accurate Page Breaks
Dim x As Long
Dim ChtObj As ChartObject
Dim ChartSheet As Worksheet
Sheets("Summary Charts").Select
Set ChartSheet = ActiveSheet
TopPos = 6

For Each ChtObj In ActiveSheet.ChartObjects
BottomPos = TopPos + 19
With ChtObj.Chart
.Shapes(ChtObj).Left = ChartSheet.Cells(TopPos, 1).Left
.Shapes(ChtObj).Top = ChartSheet.Cells(TopPos, 1).Top
.Shapes(ChtObj).Height = ChartSheet.Range("I" & TopPos & ":I" & BottomPos).Height
.Shapes(ChtObj).Width = ChartSheet.Range("A" & TopPos & ":I" & BottomPos).Width
End With
Next ChtObj

I get a "Runtime Error 5, Invalid call or procedure on the first .Shapes line.......
.Shapes(ChtObj).Left = ChartSheet.Cells(TopPos, 1).Left

Once this portion of the code works properly, I will add code to enter Page Breaks every x number of Rows
Can anyone help me to get this working properly?

 

[pgc01]

Hi
Welcome to the board

Can you explain where you want the charts to be located?

From your code I see that the first chart has TopPos=6. What about the second chart? and the next ones?

 

[frank_AL]

Hi
Welcome to the board

Can you explain where you want the charts to be located?

From your code I see that the first chart has TopPos=6. What about the second chart? and the next ones?

Thanks for checking in and offering to help. I think I have figured it out now. I start with the first chart at Row 6 since I have rows 1-5 defined as Print Title. I want the Charts to be 20 rows in Height.

Here is the code I just finished that appears to be working properly. My issue is that I needed to define a variable as the ChartObject.Name to use in the following lines of code. All that is left for me to do now is define a variable to determine the bottom right cell value for the print range.

Let me know if you see any issues, but first time through this code gave me what I was expecting.

Dim ChtObj As ChartObject
Dim ChartSheet As Worksheet
Sheets("Summary Charts").Select
Set ChartSheet = ActiveSheet
' Set ChtObj = ActiveSheet.ChartObjects
TopPos = 6


For Each ChtObj In ActiveSheet.ChartObjects
BottomPos = TopPos + 19
ChartName = ChtObj.Name
ActiveSheet.Shapes(ChartName).Left = Sheet6.Cells(TopPos, 1).Left
ActiveSheet.Shapes(ChartName).Top = Sheet6.Cells(TopPos, 1).Top
ActiveSheet.Shapes(ChartName).Height = Sheet6.Range("A" & TopPos & ":A" & BottomPos).Height
ActiveSheet.Shapes(ChartName).Width = Sheet6.Range("A6:I6").Width
TopPos = TopPos + 21
Next ChtObj