Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Boxes and Worksheet Events
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 Pearsons 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Boxes and Worksheet Events
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 Pearsons 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Combo boxes to change options based on other Combo boxes. | New Users to Excel | |||
How to clear a group of combo boxes after a submit to another worksheet | Excel Discussion (Misc queries) | |||
Best way to populate worksheet from 2 combo boxes | Excel Worksheet Functions | |||
Combo Boxes built with Control Toolbox on a Worksheet | Excel Programming | |||
Using SUM with worksheet range selected by two combo boxes | Excel Programming |