Apr 12 2021 03:38 AM
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 04:08 AM
SolutionLet'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.
Apr 14 2021 01:44 AM
Apr 12 2021 04:08 AM
SolutionLet'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.