Hi,
I have researched and modified code that allows my 4 charts on one of my worksheets to change the maximum y axis value to a number such that the series label (outside end) does not show outside the plot area. The code that modifies the chart runs while I'm on a userform interface with another sheet active in the background. This functions as it should, but the issue is that once my code is run and modifies the chart, I execute another operation on the userform which creates PDF file of data including the worksheet with the 4 charts on them but the PDF does not show the updated y-axis max so the graphs are displayed incorrectly.
The only way to fix this is for me to close the userform, click on the worksheet with the charts which re-renders the 4 charts on that worksheet, then I can go back to my userform and create the PDF report which shows correctly. I added a chart refresh function but the charts do not seem to update unless the sheet is active and the userform is closed first.
I would like to be able to update the y-axis, refresh/render the 4 charts on the worksheet (which is not the activesheet) then create the PDF report with the updated charts. Any thoughts.
Here is the code i'm using to update the chart axis:
I have researched and modified code that allows my 4 charts on one of my worksheets to change the maximum y axis value to a number such that the series label (outside end) does not show outside the plot area. The code that modifies the chart runs while I'm on a userform interface with another sheet active in the background. This functions as it should, but the issue is that once my code is run and modifies the chart, I execute another operation on the userform which creates PDF file of data including the worksheet with the 4 charts on them but the PDF does not show the updated y-axis max so the graphs are displayed incorrectly.
The only way to fix this is for me to close the userform, click on the worksheet with the charts which re-renders the 4 charts on that worksheet, then I can go back to my userform and create the PDF report which shows correctly. I added a chart refresh function but the charts do not seem to update unless the sheet is active and the userform is closed first.
I would like to be able to update the y-axis, refresh/render the 4 charts on the worksheet (which is not the activesheet) then create the PDF report with the updated charts. Any thoughts.
Here is the code i'm using to update the chart axis:
Code:
[FONT=Verdana]Public Sub AdjustVerticalAxis()
'PURPOSE: Adjust Y-Axis according to Max of Chart Data
'Adapted from SOURCE: [URL="http://www.thespreadsheetguru.com/"]www.TheSpreadsheetGuru.com[/URL]
[/FONT]
[FONT=Verdana]Dim cht As ChartObject
Dim srs As Series
Dim FirstTime As Boolean
Dim MaxNumber As Double
Dim MaxChartNumber As Double
Dim Padding As Double
[/FONT]
[FONT=Verdana]'Input Padding on Top of Min/Max Numbers (Percentage)
Padding = 0.18 'Number between 0-1[/FONT]
[FONT=Verdana]'Optimize Code
Application.ScreenUpdating = False
'Loop Through Each Chart On ActiveSheet
'For Each cht In ActiveSheet.ChartObjects
For Each cht In Worksheets("EnergyGraphs").<wbr>ChartObjects
'First Time Looking at This Chart?
FirstTime = True
'Determine Chart's Overall Max/Min From Connected Data Source
For Each srs In cht.Chart.SeriesCollection
'Determine Maximum value in Series
MaxNumber = Application.WorksheetFunction.<wbr>Max(srs.Values)
'Store value if currently the overall Maximum Value
If FirstTime = True Then
MaxChartNumber = MaxNumber
ElseIf MaxNumber > MaxChartNumber Then
MaxChartNumber = MaxNumber
End If
'First Time Looking at This Chart?
FirstTime = False
Next srs
'Rescale Y-Axis
cht.Chart.Axes(xlValue).<wbr>MaximumScale = MaxChartNumber + (MaxChartNumber * Padding)
cht.Chart.Refresh
Next cht[/FONT]
[FONT=Verdana]'Optimize Code[/FONT]
[FONT=Verdana] Application.ScreenUpdating = True
[/FONT]
[FONT=Verdana]End Sub [/FONT]
