Excel Chart arrangement

Posted on 2003-02-20
Medium Priority
Last Modified: 2012-06-21

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
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.

Question by:SheepDip
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 7987374
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
    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

Author Comment

ID: 7992403
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
   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

Accepted Solution

bullethead earned 150 total points
ID: 8007534
Glad I helped.

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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
New style of hardware planning for Microsoft Exchange server.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question