Chart created with a macro changes location according to screen resolution.

I have a macro that creates a chart aligned with a set of cells.  I
another user runs this macro with a different screen resolution th
chart covers a different area of the spreadsheet partially covering th
data.  Also if the other users have additional or less toolbars, th
location of the chart is offset.  Is there a way to assign the locatio
of a chart to cells and not screen location.

Thanks as always

--
Message posted from http://www.ExcelForum.com

0
5/10/2004 9:27:37 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
669 Views

Similar Articles

[PageSpeed] 40

Use something along the lines of:

Sub testIt()
    With ActiveSheet.ChartObjects(1)
        .Left = Cells(3, 3).Left
        .Top = Cells(3, 3).Top
        .Height = 200
        .Width = 300
        End With
    End Sub

-- 
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <help_wanted.1627bz@excelforum-nospam.com>, help_wanted 
<<help_wanted.1627bz@excelforum-nospam.com>> says...
> I have a macro that creates a chart aligned with a set of cells.  If
> another user runs this macro with a different screen resolution the
> chart covers a different area of the spreadsheet partially covering the
> data.  Also if the other users have additional or less toolbars, the
> location of the chart is offset.  Is there a way to assign the location
> of a chart to cells and not screen location.
> 
> Thanks as always.
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 
> 
0
5/11/2004 12:02:54 AM
You can adjust the code to do this automatically. A recorded macro will 
make a chart sheet first, then change its location to a sheet. When it 
moves the chart, it sticks it in the middle of the active window (active 
pane if you have split panes activated), about half the width and height 
of the window's usable height.

Instead of this two step process using Charts.Add, use this:

   ActiveSheet.ChartObjects.Add left, top, width, height

where left, top, width, height are dimensions and positions in points.

There's more about VBA and charting here:

   http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Tushar Mehta wrote:

> Use something along the lines of:
> 
> Sub testIt()
>     With ActiveSheet.ChartObjects(1)
>         .Left = Cells(3, 3).Left
>         .Top = Cells(3, 3).Top
>         .Height = 200
>         .Width = 300
>         End With
>     End Sub
> 

0
DOjonNOT (619)
5/11/2004 5:14:24 PM
Jon, I found this page of yours more helpful for this type of situation:
http://peltiertech.com/Excel/ChartsHowTo/ResizeAndMoveAChart.html

Basically what Tushar posted.
-- 
   Toby Erkson
   Oregon, USA
   Excel 2002 in Windows XP

"Jon Peltier" <DOjonNOT@SPAMpeltiertechME.com> wrote in message
news:40A109F0.20503@SPAMpeltiertechME.com...
> You can adjust the code to do this automatically. A recorded macro will
> make a chart sheet first, then change its location to a sheet. When it
> moves the chart, it sticks it in the middle of the active window (active
> pane if you have split panes activated), about half the width and height
> of the window's usable height.
>
> Instead of this two step process using Charts.Add, use this:
>
>    ActiveSheet.ChartObjects.Add left, top, width, height
>
> where left, top, width, height are dimensions and positions in points.
>
> There's more about VBA and charting here:
>
>    http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> Tushar Mehta wrote:
>
> > Use something along the lines of:
> >
> > Sub testIt()
> >     With ActiveSheet.ChartObjects(1)
> >         .Left = Cells(3, 3).Left
> >         .Top = Cells(3, 3).Top
> >         .Height = 200
> >         .Width = 300
> >         End With
> >     End Sub
> >
>


0
5/11/2004 10:00:36 PM
Reply:

Similar Artilces:

How do you change the rows to columns and columns to rows.
Ok, I am stumped on how to do this? I have a spreadsheed that has Dates in the A column and Data in the row. I want to make is so, the data is in the A column and Dates ru accross in the 1 column. How can I do this? Sound easy, but I am stumped??? :eek -- cc4digita ----------------------------------------------------------------------- cc4digital's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2656 View this thread: http://www.excelforum.com/showthread.php?threadid=39843 Highlight your data and copy it into the clipboard. Move to an open spot in the sprea...

OnChange event
Hi all, I want to let the end users change the color of the top and bottom dark blue (or navy) borders of the Account form using a drop down that lists the colors (red, yellow and blue) .So the OnChange event of the picklist should trigger the change of the top and bottom dark blue colors to which ever color is selected. However I dont know if this can be accomplished. I even dont know if a stylesheet is used for that purpose, which one refers to these areas of the form. Any help will be deeply appreciated. Thanks in advance! ...

Unable to create email rules! Any suggestions...??
Hi, I would like to create a rule for Outlook 2003 email, but I find no "rules and alerts" option under the "Tools" menu. It is entirely absent, not grayed out. (When I've looked up how to create a rule, it states you click on "rules and alerts" under "Tools") Would appreciate suggestions. Do you have a mail account installed? Are you in the in-box when you look for this option? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted wi...

Macros
I'm getting confused. I built two macros for a given spreadsheet and inserted them on a custom toolbar. When we copy the spreadsheet, giving it a new name, and then open the copy and run the macros - excel opens a new spreadsheet with the name of the original. I'm guessing that the macros are assigned specifically to a particular spreadsheet somehow? How do you suggest I work around this? I'd like to copy the spreadsheet and have the macro work in the newly created spreadsheet too. Even if it entails some minor coding changes, thats fine - or is there a way to tell the macr...

Percentage Column Charts
HI!! I think I am thinking too much about this and it is easier than it seems, but I cant figure it out! hopefully someone can help. So I need to create a chart with 2 column...plan vs. actual. But I need each of these bar columns to be separted by percentages of the categories that make them up. EX: I need to make a comparison on how big of a percentage the Labor part of the Budget was when they planned and how it differs from the actual execution. Thanks for your help! Stacked Column Graph Set up as labor code ACT PLAN a 1 1 b 3 2 c 5 3 d ...

How to create 20% off coupon?
Any ideas? HI JHO, the idea is that if you have do not want to keep complete information of customer then create the customer default for coupon as "COUPON customer" and give 20% discount in customer option. Second if you don't wanted to apply this then other option is apply the specific pricing to specific customer i.e. price a,b,c,d and setup the sales prices. Let me know if this help you. "jho" wrote: > Any ideas? ...

Excel macro list
In Excel 2003 I used to be able to list all macros in a workbook by pressing Alt+F8. Now all I get is a series of ribbon help letters... What's changed? Is there still a way of accessing macros via Alt+F8? Any suggestions appreciated. Hi, ALT+F8 works for me in E2007. What do you mean by 'I get is a series of ribbon help letters' Mike "pbaker6" wrote: > In Excel 2003 I used to be able to list all macros in a workbook by pressing > Alt+F8. Now all I get is a series of ribbon help letters... What's changed? > Is there still a way of...

Unable to change password
Hi, We have some users in domain A, who now have exchange accounts in domain B. They will log on to domain A as usual, then when they open outlook it prompts them for a logon to domain B. Works fine. Problem is that we gave them all a standard PW for domain B so we could check their mail was working. We now want them to change their passwords for domain B, so we selected 'user must change PW at next logon' in the AD. Sure enough they are prompted to change password when they open outlook, but the password change is never accepted. Can anyone help? Andy Cox ...

End user wants to create items in Global Address List
I have an end user who wants to be able to create distribution lists and entries in the global address list. Any idea how I can configure him to do this? Douglas he would have to have Exchange permissions, and that isn't a particularly good idea... "Douglas McIver" <douglas@nospam.com> wrote in message news:OFGd$QhiEHA.1104@TK2MSFTNGP10.phx.gbl... > I have an end user who wants to be able to create distribution lists and > entries in the global address list. Any idea how I can configure him to do > this? > > Douglas > > ...

XY chart labeller Issue: (Magic quadrant) But with a twist.
Hiya Folks I have the same problem as has already been discussed on the forum (see below@ end of my email), but in my case there is an additional twist. The orginal issue was solved by using Rob Bovey's XY Chart Labeler to add labels. The free download is at his web site: http://www.appspro.com/Utilities/ChartLabeler.htm But in my case I have lots of XY points (155 to be precise). The problem is that 155 visible labels make the chart look very messy. Is there anyway to make the XY labels invisible until you hover over them with a mouse/pointer? Any help would be greatl...

Percent Change
I'm using the formula (A2-A1)/A1 to determine the % change between two cells. I have an instance where I need to subtract the value of two cells from the current cell before determining the % change. I've come up with several different ways to arrive at the actual changed value (e.g. A3-SUM(A1:A2) or SUM(A3-A2-A1) ) which return the correct value but when I try to arrive at the % change from the original amount by adding "/A1" to the formula I get an error. Am I way off? Tom, Could you post the end formula exactly as you typed it? It should work, based on what you'...

sort data without changing pattern
I have a worksheet where every other row has a 'pattern' in it to help make it easier to read across the spreadsheet. The problem I'm running into is as I add new rows at the bottom of the spreadsheet and then resort them into their correct place, the patterns go with the sort and now the patterns have to be redone again the get them back to every other row. Any suggestions? This will happen if your patterns are manually applied. It would be better to remove that manual shading and to apply background colours using conditional formatting, the condition being if th...

Compare 2 Worksheets Create a 3rd depending on results
I have a unique problem that none of the other posts or shareware seems to solve. I have 2 worksheets (orig.xls and new.xls) that are 15 columns wide (to the "O"). The data is just numbers but the second column either has the words: "new" or "cancel" which is important as you will see. I need to compare the two worksheets and create a third worksheet (update.xls) depending on the three possible results: 1) If a row is removed in the new.xls file = copy the row from the orig.xls file and make the 2nd column "Cancel" 2) If a row is added to the new.xls...

chart question #4
Can anyone please give me some guideline on how to draw a chart with y-axis is the count of the item in x-axis? I am using Excel 97. Thanks. I am not certain I fully understand your question, but I am going to give it a stab. Excel charts are very flexible, and I think you need to keep in mind that you need to have the right data in place to draw the chart. I.e. don't focus on the chart itself, focus on the data in the spreadsheet you are trying to chart, and once that is in place, your chart will look the way you want. -- ChrisJ Omaha "00KobeBrian" wrote: > Can...

Outlook 2003
We're on Exchange 2003 and have started rolling out Outlook 2003 in place of Outlook 2000. In Outlook 2003 by default all folders come up grouped by day and with solid lines between the messages. Everyone hates these "features" and has made a point of telling me so. Yes, each individual folder can be changed back through the user interface but that's a non-solution for 100 users that have an average of 25 folders each plus the 50 or so public folders as well. Is there any way to change this default behavior in Outlook 2003 so that all folders come up without the solid lines...

Creat a time book
I'm building a semi automated time book in Access. what i want is to be able to give access a two week period prefferably by specifying the beginning and end dates and have access add an entry to a table i'm going to call the 'Time Book' for each person in a personnell table for each day. the best i have been able to come up with is to pack a Macro with 14 queries, each adds one more day to a specified starting point. one of the problems i'm running into is that some of the shifts run over night and Access doesn't calculate the shift end correctly. I wo...

How to order chart in descending order of Freq?
I am using Access 2000-03. The graph is a subset to excel, but very close. I have a Access Graph Chart with the Row Source set to: "TRANSFORM Sum([Freq]) AS [SumOfFreq] SELECT [Personnel] FROM [Rept#704_Joint_Personnel] GROUP BY [Personnel] PIVOT [RP];" Currently, my graph order the personnel in Ascending order of Personnel. I want to now order it by Descending Order of Freq. In the query [Rept#704_Joint_Personnel], I have the following fields: a) Freq b) Personnel Can someone please help me here! Thank You, Gary I don't know how you'd have to modify your ...

Damn the accept/change downloaded transactions feature!!!
I recently upgraded from Money 2001 to Money 2004 and I HATE IT! My favorite feature of Money was the ability to download all my transactions electronically from my bank, and then decide how they would be categorized and entered into my account register. Now it's so much more complicated with this "Accept/Change" stuff!!! I realize this has been around since Money 2002, but I'm just experiencing this horror now. I HATE the way it just puts new transactions right into your register! And what the heck does "Accept" or "Change" mean anyway? Do they mean...

How to create a scrollable dialog box ?
Hi I've created a dialog resource (it uses CDialog as the base class) that is longer than the height on the screen (this is for WinCE, but the question still applies to desktop windows). I want the user to be able to scroll down the dialog filling in data in editbox's as they go. How do I get the dialog to be scrollable (i.e. a scroll bar at the right hand side that works as a CScrollView would). In eVC++ (and in VC6 also) there is a box to tick of a vertical scrollbar (in the resource editor) but the scroll bar seems to be disabled at run time. Are there some window messages that ...

How do i locate the point of intersection of two lines graphs
I have a plot of two lines using excell which intersect at a point. I want to locate the point of their intersection using excell and show the co-ordinates of that point so that any body can see that i read that point from it. Step by step procedurewould be helpful and i am using microsoft excel 2007. Thanks. I will assume you have two straight lines - if not please tell me what they are. Let the lines follow the two functions y1=m1x1+b1 and y2=m2x2+b2 Let the point of intersection be Y and X Then m1X+b1=m2X+b2, giving X=(b2-b1)/(m1-m2) In Excel terms, X=(INTERCEPT(y-values2,x-values2)...

how to find on screen amimated character
how do u get an animated screen character to come up on ur screen? i have windows vista Hello LFGuard395, I'm sorry there are no animated screen characters in the Microsoft Project software, so I am guessing that you are not discussing Project? I suggest looking again at the software program you are trying to view the animated screen character in (Word?, Excel?) and then reposting your question to the appropriate newsgroup. See the listing at: http://social.answers.microsoft.com/Forums/en-US/group/Office I hope this helps. Let us know how you get along. Julie Pr...

Excel Graphing Line References off when chart is a sheet.
I have noticed that when any graph is created in EXCEL and you hover you mouse over the dataline you receive that corect response. If you convert the chart to a sheet, the hover of the data line is now not representative of the the y axis directly below it. The data being graphed is correct now the hover represents the "series" (x-Axis) correctly but does not represent the "Point" (y-axis) correctly at all. Tne Y-axis datapoint reference is wrong. Any help? ...

Allow Account Description changes in "Mass Modify" screen
In the "Mass Modify" screen of Account Maintenance "Cards", there should be a selection for Description changes. Some of our Accounts have 45-50 account combinations. If an account needs to be renamed, I have to go into each card to update. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click ...

Changing the Normal Style
How can I chnage the Normal style in Excel 2000 so that new worksheets begin with the cell formatting set to center vertically? Styles are stored with each workbook. So you could create a workbook that has the normal style you want. Then save this workbook as a template for all new workbooks. If that's what you want to do: Start a new workbook. Format|style|Modify (and modify it to your heart's content) Also, modify anything else you want as a standard--header/footers, page orientation, number of worksheets in that workbook??? Now save it as a template in your XLStart folder as ...

How to create drop down list that determines another drop down list?
Hey all, Here is an example of what I am trying to do. I have a list of ten baseball teams with 20 players on each team. I want to create a drop down box that will allow me to select one of the ten teams and then a second drop down box below the first that will give me the list of the 20 players on the team I have selected in the first box. Then I want to choose that player to enter his statistics. Is this possible? Any help is much appreciated. THANKS in advance, Darren D. Darren You can use the selection from the first pulldown as the defined name for the second selection list Then in t...