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.

   Sheet1.ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = Sheet2.Range("A1")
   Sheet1.ChartObjects(1).Chart.Axes(xlValue).MaximumScale = Sheet2.Range("A2")
   Sheet1.ChartObjects(1).Chart.Axes(xlCategory).MinimumScale = Sheet2.Range("B1")
   Sheet1.ChartObjects(1).Chart.Axes(xlValue).MinimumScale = Sheet2.Range("B2")

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.

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.

Originally Posted by JFistere

I did some more testing and the statement

MsgBox Sheet1.Range("B2")

produces the same error.

MsgBox [b2].Value

displays the date correctly.

Originally Posted by JFistere

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?