Change Excel Axis value

Posted by Rampino under VB.NET on 12/26/2011 | Points: 10 | Views : 3204 | Status : [Member] | Replies : 1
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




Responses

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

Up
0
Down
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

Login to post response