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

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

excel

HansVogelaar

Apr 12, 2021

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.