I would **profoundly appreciate** assistance from anyone regarding dynamically updating the X-Axis value of an Excel Bar-Chart via EITHER in-sheet formulae OR via VBA-code.
**I've unsuccessfully tried the following**:
---Created a named-range on the 3 in-sheet cells (Q2, R2, & S2) which will always contain the occassionally updated values for:
X-Axis-Minimum,
X-Axis-Maximum, and
X-Axis-Major-Units.
Then typed the following formula into each of the respective /Format.Axis/Axis.Options dialog-interface data-boxes for all of those 3 variables...:
=MAIN!XMIN
=MAIN!XMAX
=MAIN!XUNITS
...respectively, where "MAIN" is the name I've assigned to Sheet1.
However, the dialog-interface data-boxes do not retain the formulas, but simply revert back to whatever data was previously in there.
**Alternatively, I've tried to solve this via VBA using the following algorithm variations tied to an ActiveX-Control button named "ReCalibrateButton".**
Please help me determine which of the following algorithm iterations is most efficient and closest to accurate; as well as what's missing or wrong and preventing it from working successfully:
**(Algorithm #1)**
==============================================================
Option Explicit
Private Sub ReCalibrateButton_Click()
Dim wsChart As Chart
Dim wsInput As Worksheet
Set wsChart = EAMPVPMSChart
Set wsInput = ThisWorkbook.Sheets("MAIN")
With wsChart
With .Axes(xlCategory)
.MinimumScale = wsInput.Range("Q2").Value
.MaximumScale = wsInput.Range("R2").Value
.MajorUnit = wsInput.Range("S2").Value
End With
End With
End Sub
==============================================================
When run, this algorithm unfortunately yields the following error= "Compile Error: Variable not defined"
==============================================================
What have i missed or done wrong within this algorithm?
==============================================================
**(Algorithm #2)**
==============================================================
Option Explicit
Private Sub ReCalibrateButton_Click()
Dim objCht As ChartObject
For Each objCht In ActiveSheet.ChartObjects
With objCht.Chart
' Value (X) Axis
With .Axes(xlCategory)
.MinimumScale = ActiveSheet.Range("Q2").Value
.MaximumScale = ActiveSheet.Range("R2").Value
.MajorUnit = ActiveSheet.Range("S2").Value
End With
End With
Next objCht
End Sub
==============================================================
When run, this algorithm unfortunately yields the following error= "Run-time error '-2147467259 (80004005)' Method 'MinimumScale' of object 'Axis' failed"
==============================================================
What have i missed or done wrong within this algorithm?
==============================================================
**(Algorithm #3)**
==============================================================
Option Explicit
Private Sub ReCalibrateButton_Click()
Dim wsChart As Chart
Dim wsInput As Worksheet
Set wsChart = ThisWorksheet.Charts("EAMPVPMSChart")
Set wsInput = ThisWorkbook.Sheets("MAIN")
With wsChart
With .Axes(xlCategory)
.MinimumScale = wsInput.Range("Q2").Value
.MaximumScale = wsInput.Range("R2").Value
.MajorUnit = wsInput.Range("S2").Value
End With
End With
End Sub
==============================================================
When run, this algorithm unfortunately yields the following
error= "Compile Error: Variable not defined"
==============================================================
What have i missed or done wrong within this algorithm?
==============================================================
**(Algorithm #4)**
==============================================================
Option Explicit
Private Sub ReCalibrateButton_Click()
Dim wksCharts As Worksheet
Dim oChrtObj As ChartObject
Set wksCharts = Worksheets("MAIN")
With wksCharts.ChartObjects("EAMPVPMSChart").Chart
oChrtObj.Chart.Axes(xlCategory).MinimumScale = ActiveSheet.Range("Q2").Value
oChrtObj.Chart.Axes(xlCategory).MaximumScale = ActiveSheet.Range("R2").Value
oChrtObj.Chart.Axes(xlCategory).MaximumScale = ActiveSheet.Range("S2").Value
End With
End Sub
==============================================================
When run, this algorithm unfortunately yields the following
error= "Run-time error '91': Object variable or With block variable not set"
==============================================================
What have i missed or done wrong within this algorithm?
==============================================================
**(Algorithm #5)**
==============================================================
Option Explicit
Private Sub ReCalibrateButton_Click()
ActiveSheet.ChartObjects("EAMPVPMSChart").Activate
With Application.ActiveChart.Axes(xlCategory, xlPrimary)
.MinimumScale = wsInput.Range("Q2").Value
.MaximumScale = wsInput.Range("R2").Value
.MajorUnit = wsInput.Range("S2").Value
End With
End Sub
==============================================================
When run, this algorithm unfortunately yields the following
error= "Compile Error: Variable not defined"
==============================================================
What have i missed or done wrong within this algorithm?
==============================================================
