Home

Excel VBA: Dynamically update a Charts X-Axis Min, Max, & Unit Values

jackstorm13579
Regular Visitor

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?

==============================================================

 

2 Replies

Hi Jack,

 

Alg. 1: I expect there is no sheet having a codename "EAMPVPMSChart". A worksheet can have two names, the one you see on the tab (shown as "Name" property) and the one you can use in code directly (shown as "(Name)" property).

Alg. 2: Not sure, it appears to work for me

Alg. 3: ThisWorksheet is not an existing object, you're looking for ActiveSheet

Alg.4: Not sure

Alg. 5: I suspect wsInput is the problem, it is treated as a variable and was not declared.

Highlighted

Alg 2: Works for me, make sure you set the xaxis to accept dates (numbers).

Related Conversations