working on short piece of VBA code. Situation: 200+ charts in workbook need some update ( for example Y axis scale).
I managed to write two Sub's:
- one updating charts on Worksheets ( beside tables)
- one updating standalone charts on Chart sheets
Would be nice to have solution that would dynamically test Current sheet type and each sheet type in a loop and select appropriate/run appropriate Sub for either chart on worksheet or standalone.
Any suggestion?
Sub Chart_update()
Dim sht As Worksheet
Dim CurrentSheet As Worksheet
Dim myValue As Variant
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Set CurrentSheet = ActiveSheet
myValue = InputBox("Input min value for secondary Y axis i.e 0.02 for 2%", " Sec axix min input", 0)
If myValue = "" Then
MsgBox "You changed your mind?" ' exit in case of CANCEL
Else
For Each sht In ActiveWorkbook.Worksheets
For Each Cht In sht.ChartObjects
Cht.Activate
ActiveChart.Axes(xlValue, xlSecondary).MinimumScale = myValue
Next Cht
Next sht
End If
CurrentSheet.Activate
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
Bookmarks