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.

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

Thanks to all concerned.

Regards

Phil


"Philip J Smith" wrote:

> 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.
>