SET Stock Chart Axis Max & Min on cell value

[MSlattery]

Hi:

Is is possible to set the price axis, min and max from calculated values of two cells within the spreadsheet?

I have not found a way to link those Chart Axis Format (SCALE) input boxes with the cells in the spreadsheet. They will not accept any kind of a formula that would allow a direct links between the chart axis parameters and a cell in the sheet.

Hope that I am wrong but it looks like VBA is the only answer.

Thanks for any direction,

Michael

[3com]

Related info

http://support.microsoft.com/kb/214075/en-us

code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
    Case "$E$2"
        ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
            .MaximumScale = Target.Value
    Case "$E$3"
        ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
            .MinimumScale = Target.Value
    Case "$E$4"
        ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
            .MajorUnit = Target.Value
    Case "$F$2"
        ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
            .MaximumScale = Target.Value
    Case "$F$3"
        ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
            .MinimumScale = Target.Value
    Case "$F$4"
        ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
            .MajorUnit = Target.Value
    Case Else
End Select
End Sub

HTH.

[MSlattery]

Hi 3Com: Thanks for you work. I read the microsoft support page you referenced and between your code and those notes I am confused. (I am not very good with VBA I am sure the confusion is all mine)
Your code refers to six addresses within the worksheet. E2, E3, E4, F2, F3 and F4?

I have two addresses that need to be referenced, the first is in B7 and indicates the MAX range, the second is the MIN range in cell B8.

The chart type is a OHLC stock chart and the price values are plotted on the secondary plot.

From another site I found the following very simple code which does not work for me, but apparently does for someone somewhere?

Sub SetYScale()
    With ActiveSheet.ChartObjects("Chart 4").Chart.Axes(xlValue)
        .MinimumScale = Sheets("Sheet.Name").Range("B8").Value
        .MaximumScale = Sheets("Sheet.Name").Range("B7").Value
End With
End Sub

Thanks for you help, Michael

[vbfbryce]

What happens when you try that code? What error do you get? Is it that you don't have a "Chart 4?"

[MSlattery]

Hello vbfbryce:

Runtime Error: '-2147352565 (8002000b)':

"The specified dimension is not valid for the current chart type."

When you click the debug button the following code is highlighted in yellow.

.MinimumScale = Sheets("Sheet.Name").Range("B8").Value

When click on the stock chart, in the ref box it come us as Chart 4.

Don't know if this is relevant, but the sheet reference may be a problem. I name each tab with the trading symbol of the stock. The Yahoo Financial VBA script takes this tab name and uses it for the symbol in cell B5
using the following code. =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1,32)

The idea is that once this one sheet is complete and fully functioning I will be able to Move and Copy new sheets into the worksheet, each with a unique stock symbol as the sheets name.

Thanks for your assistance,

Michael

[vbfbryce]

What is the , value at that point?

[MSlattery]

The value in cell B7 = 19 = MAX
The value in cell B8 = 11 = MIN

Values in Chart axis are:

High range = 20 and Low rang = 0.

[3com]

anyway..

change this line...

MinimumScale = Sheets("Sheet.Name").Range("B8").Value

new line...

MinimumScale = Sheets(Sheet.Name).Range("B8").Value

notice there is not double quotes in <Sheet.Name>.

[MSlattery]

3Com:

Thanks for your assistance. Sorry, but there is still a problem?

Error Code:424 Object required

Code selected when you hit the debug button is:MinimumScale = Sheets(Sheet.Name).Range("B8").Value

Sub SetYScale()
    With ActiveSheet.ChartObjects("Chart 4").Chart.Axes(xlValue)
        MinimumScale = Sheets(Sheet.Name).Range("B8").Value
        MaximumScale = Sheets(Sheet.Name).Range("B7").Value
End With
Range("BN5").Select
Beep
End Sub

I check the chart again to double check that it was still Chart 4 and it is so thats not the problem.

I am guessing that because this is a stock chart with two vertical axes that the specific axis must be defined.

Secondary is Price, the one I want to control with this VBA and Primary is Volume.

The only reason I added the End With is a previous error code stating it was looking for it.

Michael

[vbfbryce]

I'm guessing it is because the value of sheet.name is "nothing" at that point

[MSlattery]

The name in the tab is "F" for the stock Ford. How do I check and see what the script is seeing when sheet.name is called?

Thanks, Michael

[vbfbryce]

Put a "stop" before that point and step through

[MSlattery]

I have stepped through the VBA script and it appeared that although Chart 4 was being activated in the code It was not being selected.

I manually recorded a new macro and selected the stock and then the chart axis.

I took those selections and inserted them into my marco to produce the following code.

Sub SetYScale()
    ActiveSheet.ChartObjects("Chart 4").Activate
    ActiveChart.Axes("xlValue", "xlSecondary").Select
        MinimumScale = Sheets(Sheet.Name).Range("B8").Value
        MaximumScale = Sheets(Sheet.Name).Range("B7").Value
Range("BN5").Select
Beep
End Sub

Chart 4 is being selected and you can see that selection as you step through the macro.

Now I am getting Error Code Type 13
Type Mismatch on bolded text in code

Still on the not so merry go round ;√(

Thanks Michael

[MSlattery]

RESOLVED & for future reference.

I found a script on a gummy Pivot Point calculator that contained the following Macro.

Set my sheet up to conform with the inputs the macro was looking for and obtain the desired results.

Here is that Macro

Sub UpdateScale()
Dim ChartVar As chart
Dim lMax As Long, lMin As Long

On Error GoTo ScalingProblem
    'Assigns the values in the Min and Max ranges to variables.
    With Sheet1
        lMax = .Range("Max").Value
        lMin = .Range("Min").Value
        'Creates chart object.
        Set ChartVar = .ChartObjects("Chart 48").chart
     
            
               With ChartVar.Axes(xlValue, xlPrimary)  'Adjusts the price axis
                   .MinimumScale = lMin
                   .MaximumScale = lMax
               End With
            
    End With
Exit Sub

ScalingProblem:
RetrievalProblem:
    MsgBox "Unable to update chart scale.", vbCritical + vbOKOnly, "Scaling Error"
End Sub

Thanks to all who made suggestions,

Michael

[Frabulator]

I apologies for bringing up a dead post, however I feel I should share my findings incase anyone else is looking up the same scenario I am.

I was needing to change the major unit load (among other things) in an Excel Chart via VB.Net and was having a difficult time doing so.
MSlattery’s bolded quote lead me to a solution (so thanks).
In case anyone is wondering this is how you accomplish the task:

Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim xlChart As Microsoft.Office.Interop.Excel.Chart
        Dim chartPage As Excel.Chart
        Dim xlCharts As Excel.ChartObjects
        Dim theChart As Excel.ChartObject
 
            xlApp = New Microsoft.Office.Interop.Excel.Application
            xlApp.Application.DisplayAlerts = False
            xlWorkBook = xlApp.Workbooks.Add()
            xlWorkSheet = CType(xlWorkBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
 
            'make sure it is not visible
            xlApp.Visible = False
            xlApp.ScreenUpdating = False
            xlApp.DisplayAlerts = False
 
            Dim rng As Microsoft.Office.Interop.Excel.Range
‘rubbish code
                Try
                    rng = xlWorkSheet.Range("E" & RowStart + 2 & ":F" & dtCOPY.Rows.Count + RowStart + 1)
                    rng.Select()
                    xlCharts = CType(xlWorkSheet.ChartObjects, ChartObjects)
                    theChart = xlCharts.Add(300, 45, 500, 350)
                    chartPage = theChart.Chart
                    chartPage.ChartType = Excel.XlChartType.xlXYScatterSmooth
                    chartPage.SetSourceData(rng)
                Catch ex As Exception
                    MsgBox("Error Graphing Data. Please insure that your amount of data points do not exceed Excel's limit of 255.")
                End Try
 
 
                        With chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary)
 
                            .MajorUnit = 0.5
 
                        End With