Change Chart Y-Axis Scale via VBA, Chart only refreshes when sheet is made active...

RWGH5

New Member
Joined
Jan 30, 2018
Messages
1
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:

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]



 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Watch MrExcel Video

Forum statistics

Threads
1,129,513
Messages
5,636,773
Members
416,939
Latest member
Rajakumaran

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top