SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2267686%22%20slang%3D%22en-US%22%3EAutomatically%20Set%20Min%20range%20on%20a%20Bar%20chart%20based%20on%20a%20cell%20value%20%7C%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2267686%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20trying%20to%20create%20a%20bar%20chart%20which%20automatically%20updates%20the%20Minimum%20Bound%20based%20on%20a%20Value%20Cell.%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20to%20do%20it%20with%20VBA%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%3CBR%20%2F%3ERedi%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2267686%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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 Replies
best response confirmed by RediM (New Contributor)
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.