Change Excel Axis value

Posted by Rampino under VB.NET on 12/26/2011 | Points: 10 | Views : 4820 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

I created an add-in for Excel, with the button1 insert formulas, and create graphs on the sheet, with the second button, I would like to change the value axis (2) of the first Chart

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Button1.Click
  Dim SheetDatas As Excel.Worksheet = CType(Globals.ThisAddIn.Application.Worksheets(WORKSHEET_DATAS), Excel.Worksheet)
  SheetDatas = CType(Globals.ThisAddIn.Application.ActiveSheet, Excel.Worksheet) 

'add number, formulas and Charts
'add chart 1
Dim chartsEX As Excel.Chart
Dim ObjChart As Excel.ChartObjects
Dim Chartmy As Excel.ChartObject
Dim RangeEX As Excel.Range

xlCharts = SheetDatas .ChartObjects
        Chartmy = ObjChart.Add(20, 20, 30, 30) 
        chartsEX = Chartmy.Chart
'insert series
'I post the minimum of the scale that I would change after the button2
chartsEX.Axes(2).MinimumScale = SheetDatas.Cells(1, 8).value 

'add chart 2
xlCharts = SheetDatas .ChartObjects
        Chartmy = ObjChart.Add(20, 80, 30, 30) 
        chartsEX = Chartmy.Chart
'insert series

'add chart 3
xlCharts = SheetDatas .ChartObjects
        Chartmy = ObjChart.Add(20, 120, 760, 270) 
        chartsEX = Chartmy.Chart
'insert series

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Button2.Click
Dim SheetDatas As Excel.Worksheet = CType(Globals.ThisAddIn.Application.Worksheets(WORKSHEET_DATAS), Excel.Worksheet)
SheetDatas = CType(Globals.ThisAddIn.Application.ActiveSheet, Excel.Worksheet) 

SheetDatas.Cells(38, "D").value = 100

SheetDatas.ChartObjects(1).Select()
SheetDatas.ChartObjects(1).Delete()

'everything works

I can not select the axis of chart1, and then I can not change it
I tried various things but does not work

SheetDatas.ChartObjects(1).Axis(2).Select()   
chartsEX(1).Axes(2).MinimumScale = SheetDatas.Cells(1, 8).value

I can not figure out where mistake, I need help

Regards

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Rampino on: 12/28/2011 [Member] Starter | Points: 25

0	this is a macro in VBA for change axis min/max 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. lMax = Sheets("dati").Range("Max").Value lMin = Sheets("dati").Range("Min").Value Sheets("grafico").Select() ActiveSheet.ChartObjects("Grafico 1").Activate() ActiveChart.Axes(xlValue).Select() With ActiveChart.Axes(xlValue) .MinimumScale = lMin .MaximumScale = lMax .MinorUnitIsAuto = True .MajorUnitIsAuto = True .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone End With 'ScalingProblem: 'RetrievalProblem: ' MsgBox("Unable to update chart scale.", vbCritical + vbOKOnly, "Scaling Error") I need help Rampino, if this helps please login to Mark As Answer. \| Alert Moderator

this is a macro in VBA for change axis min/max

 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.



           lMax = Sheets("dati").Range("Max").Value

           lMin = Sheets("dati").Range("Min").Value





           Sheets("grafico").Select()

           ActiveSheet.ChartObjects("Grafico 1").Activate()

           ActiveChart.Axes(xlValue).Select()

           With ActiveChart.Axes(xlValue)

              .MinimumScale = lMin

              .MaximumScale = lMax

              .MinorUnitIsAuto = True

              .MajorUnitIsAuto = True

            

              .ReversePlotOrder = False

              .ScaleType = xlLinear

              .DisplayUnit = xlNone

           End With



    

    'ScalingProblem:

    'RetrievalProblem:

    '        MsgBox("Unable to update chart scale.", vbCritical + vbOKOnly, "Scaling Error")

I need help

Rampino, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts