+ Reply to Thread
Results 1 to 9 of 9

Excel 2007 Setting chart axes in VBA

  1. #1
    Registered User
    Join Date
    02-18-2011
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Excel 2007 Setting chart axes in VBA

    I have sets of data and I want to set the horizontal (Date) axis and the vertical axis Maximums and Minimums of scatter charts to cell values I have calculated. I have not been able to make it work consistently.

    I have a few questions:

    1. What is the VBA code that will set the Axes limits of scatter charts to cell values?

    2. On the Code page at the top, I assume I should select Worksheet. What Procedure (upper right) should I use? Why?

    3. If macros are off for some reason, I can turn them on when the workbook is first opened. How can I make sure macros are on and set them if they are not?

    Thank you,

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,647

    Re: Excel 2007 Setting chart axes in VBA

    Welcome to the forum!

    Judging from your questions you may not be too familiar with VBA programming. I'll try to give you some complete answers but you may need to seek more foundational knowledge outside of this thread.

    1. What is the VBA code that will set the Axes limits of scatter charts to cell values?

    The following expressions can be used to set the scale of the axes. The (1) is the index to which chart you're talking about. Each worksheet can have a collection of charts. In this example, the code refers to the first chart on worksheet Sheet1. These are just expressions that you will have to use in other code. xlCategory is a built-in constant that refers to the X axis, and xlValue is the Y axis. Here is an example of how you would set them to values in some cells.
    Please Login or Register  to view this content.
    If you have more than one chart then you need to write a loop around this that does it for every chart. If you have many charts but only want to do this for some, then it creates yet more work.

    2. On the Code page at the top, I assume I should select Worksheet. What Procedure (upper right) should I use? Why?

    You will need to write a Sub that sets the values above, and then run it from your macro list (ALT-F8), or you could add a button to the worksheet and press it. You will create your own Sub which will then become visible in the list of Declarations (not "Procedure", not sure where you are seeing that). You probably want to put this code into a the code for a specific worksheet if there is only one worksheet that contains your charts. If they are in multiple sheets then you should create a new Module (righk-click on the file name, Insert, Module). Code in a worksheet knows it's referring to that worksheet, but code in a Module must specifically qualify the worksheet (as in my code example above).

    3. If macros are off for some reason, I can turn them on when the workbook is first opened. How can I make sure macros are on and set them if they are not?

    You have to set your Excel macro security level to "Disable macros with notification" if you want to allow macros to run. If you open a file and decline to allow macros to run, then you have to close it and reopen to all allow them.
    Jeff
    | | |?| |?| |?| |?| | |:| | |?| |?|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    02-18-2011
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel 2007 Setting chart axes in VBA

    Thanks, Jazzer -

    Your observation about my VBA skills is spot on. I have just bought "Excel 2007 VBA Macro Programming" by Richard Shepherd, and am starting from Chapter 1, doing the examples.

    When I run the code you recommend, I get "Run-time error '424' Object required". The only difference is that that the four parameters are in Sheet1 so all eight references are to Sheet 1.

    The chart is named Chart1. I've tried variations of your code, with and without subscripts, and using the chart name, but I can't get it to work. Any suggestions?

    Thanks,
    John

  4. #4
    Registered User
    Join Date
    02-18-2011
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel 2007 Setting chart axes in VBA

    I did some more testing and the statement

    MsgBox Sheet1.Range("B2")

    produces the same error.

    MsgBox [b2].Value

    displays the date correctly.

  5. #5
    Registered User
    Join Date
    02-18-2011
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel 2007 Setting chart axes in VBA

    I should have added that the change did not solve the problem with the complete statement.

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,647

    Re: Excel 2007 Setting chart axes in VBA

    I set up a scatter chart and tested the code that I posted above and it worked as expected. If it does not work for you then I would need to see your workbook.

    Quote Originally Posted by JFistere View Post
    I did some more testing and the statement

    MsgBox Sheet1.Range("B2")

    produces the same error.

    MsgBox [b2].Value

    displays the date correctly.
    Quote Originally Posted by JFistere View Post
    I should have added that the change did not solve the problem with the complete statement.
    These two posts don't seem related to this thread, do you have another active thread where you meant to post this?

  7. #7
    Registered User
    Join Date
    02-18-2011
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel 2007 Setting chart axes in VBA

    Hi Jazzer,

    Those two posts you asked about above referred to the fact that the right side of the expressions we have been working with did not seem to work except in the [b2].Value form ... but that I was still having trouble with the complete expression.

    The form that worked for me begins with ActiveSheet rather than Sheet1 or any Sheet<any other number>.

    I got everything working, including dynamically changing scales for the five graphs in a vertical row on the sheet. I thought that I had run a test some time ago that confirmed that I could print the charts full screen on five pages, but I couldn't make it print properly now. Only one graph at a time.

    So I moved the graphs to five separate sheets. I moved the VBA to the graph sheets (only one so far) but I get errors for all the syntax variations I could think of. The typical error is

    Method '_Evaluate' of object '_Chart' failed.

    Can you help me with the VBA syntax with the chart on its own ChartN page? The book I bought does not get into manipulating chart Axes at all.

    Thanks,
    John

  8. #8
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,647

    Re: Excel 2007 Setting chart axes in VBA

    When a chart is in a Chart sheet, instead of a worksheet, you get to it differently. Here is a mod to my earlier code to do the same thing.

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    02-18-2011
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel 2007 Setting chart axes in VBA

    I'm happy to say that the application is fully operational. Thanks for your help. I really appreciate it.

    Cheers,
    John

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1