Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

This is an article on how to answer questions, earn points and become an expert.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

581 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