Set a Minimum Value for the X-Axis

Question

Currently I am manually inputting the date of a project as an excel serial number into the Axis options of my chart (Bounds => Minimum).

I am trying to place code in the worksheet to make it update this value automatically upon changing the start date.

I've spent hours on the internet trying to find the code that will work, and piecing together bits of code to do the job, but I keep getting run errors

At the moment I have got to this point with the code

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cht As Chart
Dim xmin As Integer

    Set xmin = Sheets("Planning").Range("O37").Value

    Set cht = Worksheets("Planning").ChartObjects("Chart 2").Chart

    cht.Axes(xlCategory).MinimumScale = xmin

End Sub

Thanks in advance for any help!

`Set xmin =` should be `xmin =` You may want to see [THIS](https://stackoverflow.com/questions/349613/what-does-the-keyword-set-actually-do-in-vba) — Siddharth Rout, Mar 28 '19 at 16:49
I think the `xlCategory`-Axis (usually the x-axis) has not the option to set bounds, units and scale. Do you mean the `xlValue`-axis? — FunThomas, Mar 28 '19 at 17:17
From what I was reading the xlcategory referred to the X-axis, so just thought that was the format I had to use. I guess I just need what ever is going to let me set the minimum x-axis value based on a cell value. — Mr_Col, Mar 29 '19 at 07:45
You can set the xlCategory axis minimum in a scatter chart, or in another type of chart (line, area, column) if the axis has a date scale (xlTimeScale in VBA). — Jon Peltier, Apr 02 '19 at 02:30
Ah-HA! In a horizontal bar chart, the X axis, the independent variable, is vertical, and the Y axis, the dependent variable) is horizontal. So you need to change the Y axis minimum, or `cht.Axes(xlValue).MinimumScale`. — Jon Peltier, Apr 05 '19 at 03:43
Hi Jon, thanks - I have changed it to (xlValue), however, when I tun tyhe code I get a run-time error '6', stating overflow, and highlights the line of code that says ``` xmin = Sheets("Planning").Range("O37").Value ``` — Mr_Col, Apr 08 '19 at 07:59

score 0 · Accepted Answer · answered Apr 08 '19 at 10:50

Okay - so I managed to get the following code to work as I wanted

Private Sub Worksheet_Change(ByVal Target As Range)

Dim objCht As ChartObject
   For Each objCht In ActiveSheet.ChartObjects
      With objCht.Chart
         ' Value (Y) Axis
         With .Axes(xlValue)
            .MinimumScale = Sheets("Planning").Range("E3").Value
         End With
      End With
   Next objCht

End Sub

Thanks everyone for the help and tips!

Set a Minimum Value for the X-Axis

1 Answers1