Automatically Set Min range on a Bar chart based on a cell value | Excel

[RediM]

Hi,

I was trying to create a bar chart which automatically updates the Minimum Bound based on a Value Cell.

Is there any way to do it with VBA?

Thanks in advance,
Redi

[#2 · Accepted solution]

@RediM 

Let's say the cell is B1, and the chart is named Chart 1.

Right-click the sheet tab.

Select View Code from the context menu.

Copy the following code into the worksheet module.

If you enter a value in B1 yourself:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B1"), Target) Is Nothing Then
        If Range("B1").Value <> "" Then
            ChartObjects("Chart 1").Chart.Axes(xlValue).MinimumScale = Val(Range("B1").Value)
        End If
    End If
End Sub

If B1 contains a formula, use the less efficient

Private Sub Worksheet_Calculate()
    If Range("B1").Value <> "" Then
        ChartObjects("Chart 1").Chart.Axes(xlValue).MinimumScale = Val(Range("B1").Value)
    End If
End Sub

Don't forget to save the workbook as a macro-enabled workbook.

[#3]

Thank you, it works!