Issue creating charts in excel sheet

Hi,

I had written a vb code to create multiple charts in a single Excel
sheet.

The issue which I face is that in a single sheet the charts are
getting formed ( 9 charts in a single sheet) but all the charts appear
exactly one above the other. (t seems like only one chart is
generated)  I am not able to position them such that they are placed
evenly.

Please find the code used to add charts to a sheet

The below code was placed in a loop to produce 9 charts in the sheet
'Sheet12"

With xlwCust.Charts.Add
            .SetSourceData Source:=xlsCust.Range("A1:" & strCellRef),
PlotBy:=xlColumns
            .ChartType = xlLine
            .HasTitle = False
            .Axes(xlCategory, xlPrimary).HasTitle = False
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
"Percentage"
            .Legend.Position = xlLegendPositionBottom
            .ChartType = xlLine
            .SizeWithWindow = True
            .Location Where:=xlLocationAsObject, Name:="Sheet12"
End With

Please could somebody help.

Thanks and Regards,
Sobha

0
i.sobha (6)
6/26/2007 11:47:37 AM
excel 39879 articles. 2 followers. Follow

2 Replies
393 Views

Similar Articles

[PageSpeed] 41

Sobha,

After you run your first macro, run the macro below.  Change the constants to get your desired 
spacing.  You can also change the code to make two or more columns instead of just one.

HTH,
Bernie
MS Excel MVP

Sub SpaceOutCharts2()
Dim i As Integer
Dim NumCharts As Integer
Dim myTop As Integer
Dim myLeft As Integer
Dim TopInc As Integer
Dim myCell As Range


Set myCell = Range("E1")


'Find the number of charts
NumCharts = ActiveSheet.Shapes.Count


'Specify the cell for the first chart - E1 in this example
myTop = myCell.Top
myLeft = myCell.Left


'Specify the spacing of the charts
TopInc = 255


For i = 1 To NumCharts
      With ActiveSheet.Shapes(i)
         .Left = myLeft
         .Top = myTop + TopInc * (i - 1)
      End With
Next i


End Sub




<i.sobha@gmail.com> wrote in message news:1182858457.281640.194770@a26g2000pre.googlegroups.com...
> Hi,
>
> I had written a vb code to create multiple charts in a single Excel
> sheet.
>
> The issue which I face is that in a single sheet the charts are
> getting formed ( 9 charts in a single sheet) but all the charts appear
> exactly one above the other. (t seems like only one chart is
> generated)  I am not able to position them such that they are placed
> evenly.
>
> Please find the code used to add charts to a sheet
>
> The below code was placed in a loop to produce 9 charts in the sheet
> 'Sheet12"
>
> With xlwCust.Charts.Add
>            .SetSourceData Source:=xlsCust.Range("A1:" & strCellRef),
> PlotBy:=xlColumns
>            .ChartType = xlLine
>            .HasTitle = False
>            .Axes(xlCategory, xlPrimary).HasTitle = False
>            .Axes(xlValue, xlPrimary).HasTitle = True
>            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
> "Percentage"
>            .Legend.Position = xlLegendPositionBottom
>            .ChartType = xlLine
>            .SizeWithWindow = True
>            .Location Where:=xlLocationAsObject, Name:="Sheet12"
> End With
>
> Please could somebody help.
>
> Thanks and Regards,
> Sobha
> 


0
Bernie
6/26/2007 2:50:26 PM
Hi Bernie,

Still I face issues.

NumCharts = ActiveSheet.Shapes.Count  always gives me  a zero value.

I tried getting the name of the Active sheet (Activesheet.Name) but it
does not give me any value.

Thanks,
Sobha

0
i.sobha (6)
6/27/2007 8:11:28 AM
Reply:

Similar Artilces:

Format issues
I have created a template in word which I call through a VB script from excel. The excel data is populated in the word every time I run this script. Everything works fine and the data get populated. I have a header in the template which is represents one of the fields in the excel sheet. The header shows the correct linked value for the first 10 pages but then on shows the variable name instead of the value. I have no idea why it does that? can someone help The code is as follows: For iCurrentRow = 1 To iLastRow If ActiveSheet.Range("B" & iCurrentRow...

Excel 2007: Vlookup Problem
I don't use Vlookup much and it's not working for me. I know it's user error so can you tell me what I did wrong? I have two worksheets in my file. In the first one, I have a list of addresses with a full unabbreviated US state name in column T, row 2. Column U is blank because I want to put the two-digit state code in there (starting with U2). In the second worksheet I have the full state name in Column A and the two-digit code for it in Column B (rows 1-50). Here's my formula starting in U2: =VLOOKUP(T2,StateCodes!$A$1:$B$50,2,FALSE) What is wrong? ...

How do I create a "fixed-width" datafile?
I need to create a fixed-width file from an Excel .xls file to import into my FedEx shipping software. How may I save or convert my file into a fixed field datafile? Thanks in advance, Alex If you don't have too long a line (no more than 240 characters per line), try saving a copy of your workbook as a .prn file. Look in the Save As Type box: Formatted Text (Space delimited)(*.prn) I like to change to Courier New font and resize the columns the way I like. Alex K wrote: > > I need to create a fixed-width file from an Excel .xls file to import into my > FedEx shipping...

Outlook issues after Exchange migration
We recently migrated Exchange 5.5 mailboxes to Exchange 2003 on a new domain. This was performed with the exmerge utility in a 2 step process. Now there are 2 issues with Oulook: 1) When a user types a recipient name into the address field, the name autocompletes as normal. But when the mail is sent, a message returns saying it is undeliverable. However, if the user goes into the Contacts folder and from there selects the same user to send a message to, it is successful. 2) If a user shares a calendar, specifying the user with whom to share and the level of permissions, all users in ...

Editing Chart Data Table
I have a bar chart with 2 different series of data (Item cost in $, and % of total volume delivered). I want to create a data table under the chart, but have it only show the values for the volume delivered and not the item cost. Is there a way to exclude data from the data table? Thanks. ...

Excel Crashing with Outlook
Can anyone advise me what the problem is and how to fix it with Excel. What happens is I might be working on a spreadsheet in Excel and then go to outlook to send an email and Excel then crashes causing me to lose all my work. I know one should regularly save one's work, but to have to do it because you're sending an email is just rediculous. Does anyone know the solution. ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - ...

How do I create an hourly daily planner?
I am using excel 2003. From start to finish, how can I create an houly daily planner? Why not use Outlook's Calender or Tasks, it is already set up "zed" wrote: > I am using excel 2003. From start to finish, how can I create an houly daily > planner? Hi what exactly are your requirements? Cheers JulieD "zed" <zed@discussions.microsoft.com> wrote in message news:D9D28DB8-9EE8-4034-98A7-75FDB3074B2A@microsoft.com... >I am using excel 2003. From start to finish, how can I create an houly >daily > planner? ...

Tracking of Activities created in Outlook
Activities (like meetings, phone calls and tasks) created in Outlook to not appear in the History of a contact or account? How to change? Thanks everybody for help on this! ...

Creating a userform?
I would like to enter data to a speadsheet with a userform. I want enter the data in a form that will automatically insert the data to the correct cells in my spreadsheet. And then allow me to enter new data on the next line below. Is this possible? I have just put up a file for you at:- http://www.pierrefondes.com/ It is item number 42 towards the top of my home page. In here I have tried to explain, as clearly as possible, what you need to do to set up and use a Form in EXCEL 2007. There is a simple worked example for you to work through if you want to. Please d...

Taskbar issue
Hello All I'm hoping someone can help me with this long-standing problem with both XP and Vista on separate laptops and every computer I've had (except I think Win98). I'm thinking there may be no solution, but what it's worth a try. With both of my machines, I have the taskbar (as well as other toolbars) set out to maximum size - typically half the screen - and to auto hide. (This makes a great program and file launcher) The issue is that the operating system decides it wants open the taskbar on its own from time to time on its own. Various things prompt this, ma...

Charting #10
I am using Excel 2007. I have a title (with a date in it) in my worksheet and it is the same on a graph. For my report, I always change that date on the worksheet. how do I automatically change the title in the graph. Hi Charles, Click in the title on the chart and select (highlight) whatever is in there and then click up in the formula bar and enter = sign and then click in the cell containing the title that you want. -- Regards, OssieMac "Charles Eaves" wrote: > I am using Excel 2007. > I have a title (with a date in it) in my worksheet and it is the same on ...

Excel 2007 Sheet with rows more than 15000
While working with excel sheets having rows more than 15000 lines, sometimes I observe that the processing is slowed down if data more than 15000 lines is selected. However the work is takes very less time if number of rows selected are less than say 12000. I have 2GB RAM, Can someone help me on this issue? I'm not sure what type of help you're looking for. Excel 2007 is what it is, and you either need to select more than 15,000 rows or you don't. Other than purchasing and installing more RAM on the computer or buying a newer, faster computer with tons of RAM I'm not s...

DST issue
I entered the catalog item: <Feature Name="Item" CatalogItemId="Item:MS:sysgen_timesvc_dst" /> and confirmed that the device has the registry key: HKLM\Software\Microsoft\Clock\AutoDST = 1 But the device still does not automatically adjust for DST. I set the timezone to Pacific, set the time to 3/14/2010 at 1:59AM, waited for one minute...and the time changed to 2:00AM instead of 3:00AM. What am I missing? Thanks. which OS version are you using? have you updated it with the relevant qfes which deal with the changing in DST starting day? -...

get popups/alerts when i created a table from a query
I created a table using a query. Now whenever I run the query I get popups/alerts informing me that I am deleting the table and another informing me that I am added the data records to the table. The query works fine. But I can do without the popups/alerts. This database will be available for to all users. How can I avoid these popups/alerts as well as the other users. Thank you. On Thu, 14 Feb 2008 14:00:01 -0800, Ms937j <Ms937j@discussions.microsoft.com> wrote: >I created a table using a query. Now whenever I run the query I get >popups/alerts informing me that I a...

Excel 2007
One of the features that I have often used in previous versions of Excel is having a control (e.g., a spin button) in a chart. Obviously, the control would be linked to the spreadsheet from which the chart gets its data. I cannot get this to work in Excel 2007. I am using the B2TR. When I try to put the control on the chart, I get a status bar message that says to click and drag, but nothing happens. Have they eliminated this functionality? I really hope not, since this is very valuable for presentations in my finance classes. Thanks Tim - I didn't test this very thoroughly. I do...

excel front sheet
Don't know if this is possible?? I have a excel doc which basically calculates lots of different costs. I want to use a front sheet which has fields of input a "GO" button to export the data to the relevant sheet and then clears ready for the next entry. Is it possible within Excel or will my front sheet need to be done in access. If so how do I do that.?? In 2007: Click the office button > Excel Options > customize, then select commands not in the ribbon from the choose commands from toolbar, scroll until you see form and add it - you will then see an icon of a form in ...

Outlook/EX Email Issue
I'm running Windows XP with XP Professional. I cannot send email but I can recieve it using Outlook. There error says that Outlook was unable to access the SMTP server. I cannot access my email at HOME through the web email. What I can do is send/recieve email using Outlook Express AND I can access my email through my web email client outside my home. My ISP (SBC) says its not them and I'm somewhat inclined to agree with them? They say I could have a corrupted kernel32.dll file. I have turned off my Norton, deleted my account and set up a new one and re-installed XP professiona...

How do I link Excel pages to a different master Excel workbook?
I am trying to take part lists from different assemblies and link them to a master part list. Ideally one sheet from the assembly part lists will have many pages and be linked to a sheet in the master part list with the name of that specific assembly. I am operating on Midrosoft Office Version 2003. open both the master and your part list on your master if you set a cell to (="name of part list book"!A1) you can do that by clicking any cell on the other workbook with them both open its just like a formula on the sheet only instead it has the workbooks name first i hope thi...

Change column color in chart when column value is over/under goal
No use of VBA or macros expected. It is believed to be Excel chart feature. Any ideas on how to change column colors (Red/Green) if value exceeds or meets the preset goal. Eg. if goal is 4% - anything at or above 4 should show green and under 4 should be red. Assuming your data is in A2:A20 B2: =IF(A2>=4,A2,NA()) C2: =IF(A2<4,A2,NA()) copy B2:C2 down, add some labels to B1:C1, then chart B1:C20. This will give you two series, one for the aboves, one for the rest. Select each data series, right click, choose format, and set the colour as required. -- --- HTH Bob (there's ...

Extender Install Issues
GP 8.0 Extender install - I'm encountering the following: *Login to GP brings up screen: "Great Plains Extender needs to finish installing. This may take a few minutes". It has an OK button and clicking on this gets you into GP but it comes up everytime. How can we stop this from popping up all the time. *When creating a new Extender Window and I get select the Form, I get the following error: "A remove range operation on table 'PT_User_Defined_Window_Keys' failed accessing SQL data." Procedure or function zDP_EXT40101L_1 has too many arguments spec...

Pivot Charts from Excel not to update automatically
Hi there, I have a pivot chart in Excel and want to copy this to Powerpoint. Copy>Paste obviously works fine but as soon as I change the pivot table feeding the chart, e.g. adding row data it updates the chart in powerpoint. How can I prevent this from happening as I want to copy>paste different variations of the pivot chart into one powerpoint presentation? As soon as I do this it updates all graphs in the presentation to whatever the pivot chart is showing at that time... Thank you for any help! W In article <D77A8CB0-C1C5-47BD-8401-6D6658239842@microsoft.com&...

Possible Report Issue
Hi. I'm working on a database for a safety audit. The auditors would like to be able to use some of the tables and subtables as a sort of checklist for the audits. The table/subtable structure is as follows: Topic -->Subtopic -->ProgramArea -->ProgramRequirement I decided to create a report that will output that data. However, the report is only outputting information if there are ProgramRequirements already entered for a given ProgramArea. Is there a way to get it to output the ProgramArea and ProgramRequirement even if blank? Or am I going about th...

how to highlight a cell so I know which point on the chart it is?
Hi, I am curious to see if anybody could create a visual relationship between a point on the chart and the data cell. Let's say if we have 1000 points on the chart, when I point to one of them--it will show the values--but I still have no idea where the point actually comes from. Or on the other hand, when I try to hightlight some cells using formats, the best thing is to see the points on the chart also being highlighted in some way. Thanks for any hints there. Windson: I am not sure whether I can help you here. If you select a data point and you move it Excel pops up a Goal Seek...

Excel, how do I get ALT F C to work the same as ALT F Enter C ?
The above is an example, but it aplies to any menu. Before, if I pressed say, ALT F, the drop down menu would appear and I could press, say, C and get to the submenu. Now I have to press Enter, before the C, which is a bother. Thanks. On my Windows 2000 version 5.00.2195 with Excel '97 and Excel 2003 the Alt F C still works. What version software / Excel are you using? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059 View this thread: http://www....

using excel form to input data into access
Is it posible to use an excel spreadsheet to enter data into access? please reply to group You can link access to Excel. But why? before going any further, explain the reason why you would want to do this. Let us evaluate what your needs are. Access Forms are much better for data entry, for a number of reason, than Excel. What would you use acces for then if your data is being entered and stored in Excel? Explain some more so we can guide you better. -- Hope this helps, Daniel P "billb" wrote: > Is it posible to use an excel spreadsheet to enter data into acces...