Auto Update of Y axis Max, Min and Scale

P

Philip J Smith

Hi.

I have a chart in a worksheet which changes according to selections made in
a user-form using combo-boxes. These combo boxes are from the forms toolbar
rather than the control toolbox.

I have derived the following code from the cited source and modified it as
noted. I have read Chip Pearson’s notes on Events in Userform controls and
added the code “FormEnableEvents = Trueâ€.

However it does not fully work until another activity updates the worksheet.
E.g. F2 and enter.

I could add a macro button with the instruction “Click to update Chartâ€.
Can anyone indicate how I might amend the following code to make the update
happen automatically?

--------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'Base Code copied from
'http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html
' Category (X) Axis code removed as not required
' Chart name changed
' Cell References replaced by named ranges
'
'Need to ensure that events on userforms update the chart.
FormEnableEvents = True
'Identify the chart
With ActiveSheet.ChartObjects("Chart 5").Chart

' Set the characteristics for the Y Axis
With .Axes(xlValue)
.MajorUnit = ActiveSheet.Range("DScale").Value
.MinimumScale = ActiveSheet.Range("DMin").Value
.MaximumScale = ActiveSheet.Range("DMax").Value
End With

End With
End Sub
------------------

Further points.
1. The named ranges DScale, DMin, and D Max are updated by formulae.
2. The order of .MajorUnit, .MaximumScale, and .MinimumScale does not seem
to improve or impair performance.
3. The code is in the worksheet object code for the appropriate worksheet.
4. Workbook recalculation is set to automatic.
 
P

Philip J Smith

Bob Phillips suggested a change to Private Sub Worksheet_Calculate() and it
worked perfectly.

Thanks to all concerned.

Regards

Phil
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top