Multiple charts on single chart sheet

I would like to place two charts on the same chart sheet via VBA which
I can do. 

The problem is in resizing the second chart. 

The logic I am using is create a chart sheet with no chart.
Create a chart and place it on the empty chart sheet and size and
place it using .top, .left, .height and .width settings. Works
perfectly.

Now I create the second chart and place it on the same chart sheet. If
I use the same method (different values), the second chart either
sizes incorrectly or disappears all together. 

Here's the sample code. I have somethings hard-coded and know better
but I am interested only in the sizing methods to use on multiple
charts on a single page.

Any insights or pointers in the right direction to somewhere that has
examples of this done by VBA?

Brian Reilly, PowerPoint MVP

0
Brian8921 (27)
10/17/2007 1:52:10 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
466 Views

Similar Articles

[PageSpeed] 26

Hi Brian,

You missed of your sample code :)

This, in xl2003, creates a chartsheet with 2 chart objects. Assuming active 
cell is empty when run the chart and chartobjects will be empty but 
visible.

Sub x()

    Dim chtHolder As Chart
    Dim chtTempA As ChartObject
    Dim chtTempB As ChartObject

    Set chtHolder = Charts.Add
    With chtHolder
        Set chtTempA = .ChartObjects.Add(1, 1, 10, 10)
        Set chtTempB = .ChartObjects.Add(1, 1, 10, 10)
        With .ChartArea
            chtTempA.Height = .Height
            chtTempB.Height = .Height
            chtTempA.Width = .Width / 2
            chtTempB.Width = chtTempA.Width
            chtTempB.Left = chtTempA.Left + chtTempA.Width
        End With
    End With

End Sub

Cheers
Andy
-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Brian Reilly, MVP" <brian@notreillyand.com> wrote in message 
news:8f3ch3dbeddbdto6nrdfp8f9s4pbpi4svv@4ax.com...
>I would like to place two charts on the same chart sheet via VBA which
> I can do.
>
> The problem is in resizing the second chart.
>
> The logic I am using is create a chart sheet with no chart.
> Create a chart and place it on the empty chart sheet and size and
> place it using .top, .left, .height and .width settings. Works
> perfectly.
>
> Now I create the second chart and place it on the same chart sheet. If
> I use the same method (different values), the second chart either
> sizes incorrectly or disappears all together.
>
> Here's the sample code. I have somethings hard-coded and know better
> but I am interested only in the sizing methods to use on multiple
> charts on a single page.
>
> Any insights or pointers in the right direction to somewhere that has
> examples of this done by VBA?
>
> Brian Reilly, PowerPoint MVP
> 

0
andy9699 (3616)
10/17/2007 2:12:00 PM
Hi Brian -

You've left out your code...

I ran the following procedure:

Sub TwoChartsOnAChart()

  Dim chtParent As Chart
  Dim chtob1 As ChartObject
  Dim chtob2 As ChartObject

  Set chtParent = Charts.Add
  With chtParent
    Do Until .SeriesCollection.Count = 0
      .SeriesCollection(1).Delete
    Loop

    Set chtob1 = .ChartObjects.Add _
        (.ChartArea.Width * 0.1, .ChartArea.Height * 0.2, _
        .ChartArea.Width * 0.35, .ChartArea.Height * 0.6)
    With chtob1.Chart
      .SetSourceData Source:=Worksheets(1).Range("Range1")
    End With

    Set chtob2 = .ChartObjects.Add _
        (.ChartArea.Width * 0.55, .ChartArea.Height * 0.2, _
        .ChartArea.Width * 0.35, .ChartArea.Height * 0.6)
    With chtob2.Chart
      .SetSourceData Source:=Worksheets(1).Range("Range2")
    End With
  End With
End Sub

The first chart was drawn as expected, but the second was too tall and wide, 
too far to the right, and too low. I could insert this (inside the With 
chtParent block) after creating the two charts:

    With .ChartArea
      chtob1.Left = .Width * 0.1
      chtob1.Width = .Width * 0.35
      chtob1.Top = .Height * 0.2
      chtob1.Height = .Height * 0.6

      chtob2.Left = .Width * 0.55
      chtob2.Width = .Width * 0.35
      chtob2.Top = .Height * 0.2
      chtob2.Height = .Height * 0.6
    End With

but I discovered that inserting DoEvents after creating the first chart and 
before creating the second makes both charts work as expected, without 
having to go back and resize.

Post back if this isn't what you meant.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
774-275-0064
208-485-0691 fax
jon@peltiertech.com
http://PeltierTech.com/
_______


"Brian Reilly, MVP" <brian@notreillyand.com> wrote in message 
news:8f3ch3dbeddbdto6nrdfp8f9s4pbpi4svv@4ax.com...
>I would like to place two charts on the same chart sheet via VBA which
> I can do.
>
> The problem is in resizing the second chart.
>
> The logic I am using is create a chart sheet with no chart.
> Create a chart and place it on the empty chart sheet and size and
> place it using .top, .left, .height and .width settings. Works
> perfectly.
>
> Now I create the second chart and place it on the same chart sheet. If
> I use the same method (different values), the second chart either
> sizes incorrectly or disappears all together.
>
> Here's the sample code. I have somethings hard-coded and know better
> but I am interested only in the sizing methods to use on multiple
> charts on a single page.
>
> Any insights or pointers in the right direction to somewhere that has
> examples of this done by VBA?
>
> Brian Reilly, PowerPoint MVP
> 


0
jonxlmvpNO (4558)
10/17/2007 2:31:39 PM
Thanks Andy and Jon,
This pints me in the right direction. Sorry about leaving out the
code. Meant to include it.

Brian Reilly, PowerPoint MVP

On Wed, 17 Oct 2007 09:52:10 -0400, "Brian Reilly, MVP"
<brian@notreillyand.com> wrote:

>I would like to place two charts on the same chart sheet via VBA which
>I can do. 
>
>The problem is in resizing the second chart. 
>
>The logic I am using is create a chart sheet with no chart.
>Create a chart and place it on the empty chart sheet and size and
>place it using .top, .left, .height and .width settings. Works
>perfectly.
>
>Now I create the second chart and place it on the same chart sheet. If
>I use the same method (different values), the second chart either
>sizes incorrectly or disappears all together. 
>
>Here's the sample code. I have somethings hard-coded and know better
>but I am interested only in the sizing methods to use on multiple
>charts on a single page.
>
>Any insights or pointers in the right direction to somewhere that has
>examples of this done by VBA?
>
>Brian Reilly, PowerPoint MVP
0
Brian8921 (27)
10/18/2007 5:05:44 PM
Reply:

Similar Artilces:

Bar Chart #6
Hi All I have a bar chart with different percentages displayed as the columns. Or target is 70%. Is it possible to have a target line added to the bar chart to show how many meet the target? In a line graph I just add a target column to create the target line but this will not work with a bar chart Any help greatly appreciated Kind regards Rexmann opps forgot to say using Excel 2003 "rexmann" wrote: > Hi All > > I have a bar chart with different percentages displayed as the columns. Or > target is 70%. Is it possible to have a target line added to the bar cha...

Pivot Chart
I have to make a pivot chart, but i want to set fixed fill color for every series (and them to stay tha same when i and or filter some data in the pivot chart) Any ideas? Thks -- Nacho Chivil´┐Ż _______________________________________________________________________ mail: ichivilo@alu.itba.edu.ar msn: nachochivilo@hotmail.com Tel. (54-11) 4792-8757 .. Loss of formatting is a known problem with pivot charts. There's information in the following MSKB article, suggests recording a macro as you apply the formatting: XL2000: Changing a PivotChart Removes Series Formatting ht...

Creating Complicated Charting
Ok, I know some one will be able to help me. I need to create a chart that is based off of 26 sheets give or take 3 or 4, this chart needs to be an advance skill level when mine is only intermediate. I need the chart to not only self expand, but I need it to also contain drop down menus, to make selection on what should show on the chart. I need to have at least 3 different drop downs, with up to 7-10 choices in each. I also need help on how to create a table that allows subcatagories in it. Such as a section named Falls needs to have 7 subsections numbered 1.1,1.2,1.3...etc...how do I ...

Importing data from multiple sources
I need to import data of members from two different sources, and then compare the data. Is there an easy way of doing this? -- Ted in Chicago You could import the data from the two sources, creating a table for each. Then, you can create a query that compares the data in the tables. "TedChicago" wrote: > I need to import data of members from two different sources, and then compare > the data. Is there an easy way of doing this? > -- > Ted in Chicago .... and as an alternative, you may be able to link to, rather than import, the data. You could ...

How do I alter the number of bar series in a combination chart?
The default combination chart had 4 bar series and 3 line series. I would like to have 5 bar series and 2 line series. Next time, don't bother with one of the built-in custom types, just start with a column chart, then one by one select the series you want to change, and use Chart Type from the Chart menu to change it. This time, just select the series to change, and use Chart Type from the Chart menu to change it. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ rjs wrote: > The default ...

Multiple login popups
I have several users who are experiencing multiple login popups when they open Outlook 2003 and connect to our Courier IMAP server. When this happens, it's a crap shoot as to whether they can send or receive email, Outlook might hang, or fail to connect to the server ("error receiving null folder" or something to that effect.) Worth noting is that each of them has a 2nd Outlook profile which they use when they're out of the office (This pofile is setup to talk to local Putty socket tunnel, but the problem I describing shows up when I've chosen their main profile). This...

Deleting Multiple Transactions in Money 2007
I have Money set up with my categories all laid out. And I like to keep the transactions for one year separate from the rest. So at the end of each year, I make a duplicate copy of my Money database, then delete all the transactions, reset the starting balances as appropriate, and start over. But it's tedious as hell deleting all the transactions one at a time. Hard on my computer keyboard too. Has anybody discovered a way to select and delete lots of transactions simultaneously? Thanks. ddc In microsoft.public.money, DeeDeeCee wrote: >I have Money set up with my categories al...

Dragging to Select Multiple Objects in Publisher
Hi, usually I can just drag my cursor across multiple objects and let go and I have a selection box, and from there I can move it. Now I can't for some reason. When I drag it across, the selection box just disappears when I let go. How do I do it? Might be experiencing video/graphics driver issues.Read the third FAQ here http://ed.mvps.org/Static.aspx?=Publisher/FAQs -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Daniel Axelrod" <Daniel Axelrod@discussions.microsoft.com> wrote in message news:437BBDB4-2E0F...

Multiple "SUM IF" functions in one formula??
I know it can be done, Excel can do anything but I'm not sure if I can even explain it. For example, I want column D2 to display a qty IF a customer number equals a specific value AND a part number equals a specific value. Basically Column A is a list of part numbers, and Row 1 is a list of customers. I need to pull how many parts each customer has ordered. Hi see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "Dax" <dax.tipton@smc.com> schrieb im Newsbeitrag news:593e3ca7.0408301337.7b8f6a35@posting.google.com... > I ...

what is the difference between normal chart and pivot chart
what is the difference between normal chart and pivot chart A pivot chart is tied in to the data in a pivot table, and as a result, is less flexible in terms of formatting and also in terms of what data you can include or exclude. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ DAZ wrote: > what is the difference between normal chart and pivot chart ...

chart "sized with window" in office 2007
Hey for every chart i ever created I set the sized with window option. Hate have pointless grey borders above and below my chart... think this is real popular. Where has this feature gone in the dreaded Ribbon?????? HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE HELP PLESE You can't set a chart in 2007 to be sized with window. It has been removed from Excel. Yeah, I m...

Multiple users in Outlook
My wife and I share our home computer, with two separate user profiles in WinXP. On our old computer, I set up folders, etc. in the two profiles, and we shared everything in Outlook. We both saw the same emails, calendar, contacts, etc. On the new computer, I can't seem to get it set up to do that. If she receives email, it only appears in "her" Outlook, and when I go into the program under my profile, I don't see it. How can I do this again? I must have forgotten something in the setup of the new computer... You both need to be using the same .pst file, which may req...

Mass change of same cell in multiple files??????
I have about 180 files that are price pages from a company we sell products from. The same cell in every file, Sheet 1 P1 is a factor that is used in formulas through out the files. I guess I am wondering if there are 1 of 2 solutions. 1) Can I mass change that P1 cell in all 180 files to be a different number instead of 1.0? or 2) Can I mass change any reference to that cell in any sheet to point to one cell that we can change anytime and only do 1 update for all 180 files? Thanks in advance Hi Jim You can do this with a macro http://www.rondebruin.nl/copy4.htm You can use a form...

Doughnut Charts
Hi, Have a doughnut chart - how do I get the numbers for each category to actually appear on the chart itself, as opposed to just appearing in the legend? many Thanks, Gary. Hi have a look at the following add-ins: http://j-walk.com/ss/excel/files/charttools.htm http://www.appspro.com/utilities/Labeler.asp -- Regards Frank Kabel Frankfurt, Germany "Gary Thomson" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:196e801c41c95$a4c62390$a401280a@phx.gbl... > Hi, > > Have a doughnut chart - how do I get the numbers for each > category to actual...

OWA and Multiple Domains #2
We just setup a spin off company as another tree in our AD forrest. Users can get into our Exchange servers from their desktop via Outlook but not via OWA. When you go either directly through the Exch server or via our front-end it fails. I am assuming it has something to do with Exch/OWA not knowing how to auth to this new domain. Thanks Again!! In IE 6&7 we need to prepend our username with our domain\. domain\username password Have you tried that? "jwilmer" <jwilmer@discussions.microsoft.com> wrote in message news:520F2FCA-6D3C-4FEB-B6B4-9337E1BF785D@micro...

Excel Charts in 2007
I want to create one chart which donot show the blank series in the X- axix Hi, I'm not clear on this one - if a series is blank it doesn't display. Why don't you show us sample data and tell us what you get with that data and what you want to get. Also the chart type. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Raiju" wrote: > I want to create one chart which donot show the blank series in the X- axix ...

View pareto chart by sorting the max number on the bottom
Is it possible to format the pareto chart by sorting the maximun number show in the bottom of each bar and ignore sort by Legend item? The order is determined by the Row Source of the chart. -- Duane Hookom Microsoft Access MVP "sq75222" wrote: > Is it possible to format the pareto chart by sorting the maximun number show > in the bottom of each bar and ignore sort by Legend item? > > ...

How do i change the axis location on a chart that has negative da.
I am charting data on a line chart that is negative. The x axis catagories are displayed below the zero line, but not below that last line of data. How do i move the catagories below the last line of data. I have used the offset but it only allows up to 1000. This isnt enough. You can leave the axis where it is, in fact, for a column chart moving the X axis will change the origin of the columns. Double click the X axis, and on the Patterns tab, for Tick Labels, choose the Low position. To move the axis itself, double click the Y axis, and on the Scale tab, change the Category Axis Crosse...

User defined charts- font size too small
I saved a user defined chart in Excel. The font size of the titles was saved as 16 pt. When I creste a new chart, using the same user defined chart, the font sizes come up as 4 pt. It somewhat defeats the purpose of having a user defined chart. Any suggestions? -- Bill B Bill, Apparently Excel does not set the chart autoscaling to false before saving the chart as user-defined. As a result, the chart font sizes appear to change when the chart is saved to the file that holds the user-defined charts. To prevent this from happening, activate the chart that you want to save as user-de...

Presenting all months in a year in a Pivot Table/Chart when all months are not included in source data
Excel 2007, Windows 7 I'm creating a series of pivot tables and charts to show price data by month in a given year for different categories of products (in different locations). If I have entries for most but not all months in a given year, is it possible to have excel render a table and chart that displays all 12 months of the calendar year even though not all are included in the source data? As it works by default, it only presents data for those months that are included in the source data. I know a work around would be to include the missing months with a blank entry in the source d...

Internet
-- Internet - Take Advantage of Multiple Windows When Surfing SUMMARY: Stop jumping back and forth between index pages by using several browser windows. Why use one web browser window when you can have multiple? If you have an adequate amount of memory, open up several web browsers to maximize your surfing experience. One great way to do this is via a start page. Select a page from where you would like to begin browsing, such as a table of contents or links page. Now, instead of left-clicking on links or entries to view other pages, right-click the links. From the pop-up menu that a...

Chart 'crowding' problem. Chart suddenly overlaps axis text.
My manager created several chart sheets on his system (currently Windows 2000, Excel 2002 I'm pretty sure...he's in a meeting so I can't get his SP#). The charts look good, have a couple added lines to split the quarter visually into three months (the X-axis is the day of the quarter), and it displays both X- and Y-axis labels. When I open them on my system (Windows XP, Excel 2002 SP 2) they look fine. But the following day when I open the workbook the charts are now a little larger and are overlapping the axis labels...very annoying! Any idea why this is happening? The workb...

Does GP allow for different Account Formats for multiple Companies
Hi All, I am setting up a new client that has 3 different companies. Two of them have the same GL framework. The third has a diffferent account framework. How can I change the account framework for the new company? Thanks for your help. -- TC If they are different databases simply go to Tools >> Setup>>Company >>Account Format. -- Leslie Vail, CPA, MCT, MVP MCBSP-Application for Microsoft Dynamics GP MCBSP-Installation and Configuration for Microsoft Dynamics GP cell: 972-814-8550 fax: 972-692-7472 "TC" wrote: > Hi All, > > I am sett...

Resize chart (Word 2007) while keeping H/W ratio?
How to resize chart (Word 2007) while keeping H/W ratio? Hi, Hold down the Shift key while sizing from the corners, not the center. Cheers, Shane Devenshire Microsoft Excel MVP "Binh" <Binh@discussions.microsoft.com> wrote in message news:A215421E-6D08-49A5-9DBA-9CA6C0A1ED5D@microsoft.com... > How to resize chart (Word 2007) while keeping H/W ratio? ...

Copying a column from several similar sheets into one sheet
I have several worksheets in one workbook and I want to merge/copy one column from each worksheet (the same column) (Like this, just imagine more columns with more data on several worksheets Date 06052003 Actual 13 FMT 12 % 87 ) and put these columns into rows so that each worksheet's data will descend vertically (EX: Date Actual FMT % 06052003 13 12 87 06062003 12 15 89 06072003 11 17 90 That's what I want, Please help me to do it. ...