Excel Chart arrangement

Hi.

I'm generating a number of excel charts (4) using VBA and publishing them as objects onto the same worksheet. I would like to control their location in the sheet and autosize them. So for example, having four charts arranged horizontally (not overlapping) in the worksheet which resize as I resize the window.

I can't find which properties can be used for this, or if something else is required. Here is an example of the code used to produce each Graph

Range("A1:D" & G).Select ' 240results = 1 year
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets(CurrentSheetName).Range("A1:D" & G), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:=CurrentSheetName
With ActiveChart.Axes(xlCategory)
 .HasMajorGridlines = False
 .HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
 .HasMajorGridlines = False
 .HasMinorGridlines = False
End With

Any help would be greatly appreciated.

Regards
SheepDipAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bulletheadCommented:
This will do what you want, but I don't believe that there is an event to watch for when you zoom the window.

You could perhaps create some code linked to buttons that zoomed your window and ran this code as well?

It should give you the idea, anyway.


Sub Fit_charts()

    mywidth = CInt((ActiveWindow.Width / 4) / (ActiveWindow.Zoom / 100))

    For C = 1 To 4
        ActiveSheet.Shapes("Chart " & C).Top = 0
        ActiveSheet.Shapes("Chart " & C).Width = mywidth - 2
    Next
   
    ActiveSheet.Shapes("Chart 1").Left = 0
    ActiveSheet.Shapes("Chart 2").Left = mywidth
    ActiveSheet.Shapes("Chart 3").Left = mywidth * 2
    ActiveSheet.Shapes("Chart 4").Left = mywidth * 3
   
End Sub
0
SheepDipAuthor Commented:
Thanks Bullethead! That had started to become a pain in the butt. The finalised version looks like this...

Decided to pass on the zoom buttons for now :).

Sub Fit_charts()

   ActiveWidth = ActiveWindow.Width
   ActiveHeight = ActiveWindow.Height
   myHeight = CInt((ActiveWindow.Height / 4) / (ActiveWindow.Zoom / 100))
   
   'mywidth = CInt((ActiveWindow.Height / 2) / (ActiveWindow.Zoom / 100))

   For C = 1 To 4
       ActiveSheet.Shapes("Chart " & C).Top = 0
       ActiveSheet.Shapes("Chart " & C).Left = 0
       ActiveSheet.Shapes("Chart " & C).Width = ActiveWidth * 2
       ActiveSheet.Shapes("Chart " & C).Height = ActiveHeight / 2
   Next
   
   ActiveSheet.Shapes("Chart 1").Top = 0
   ActiveSheet.Shapes("Chart 2").Top = myHeight * 2
   ActiveSheet.Shapes("Chart 3").Top = myHeight * 4
   ActiveSheet.Shapes("Chart 4").Top = myHeight * 6
   
End Sub
0
bulletheadCommented:
Glad I helped.

Was there anything else?  If not you should accept my answer so this question gets closed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.