Forum Discussion
RediM
Apr 12, 2021Copper Contributor
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
- Apr 12, 2021
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.
HansVogelaar
Apr 12, 2021MVP
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.
- RediMApr 14, 2021Copper ContributorThank you, it works!