Change minimumscale with the change event of a combobox

[Herbert Chan]

Hello,

I'm using Excel97 and Windows 98SE.

I have a chart in a a worksheet in a workbook and there is a combobox on the
same worksheet to control which series to be plotted on the chart.

I want to be able to change the minimumscale of the chart with respect to
the minimum value to be plotted and I'd like to have control over it rather
than leaving it up to Excel's auto feature.

I've tried the following code but it gave me error 1004:

Private Sub ComboBox1_Change()
Sheets("Chart").ChartObjects(1).Chart.Axes(xlValue).MinimumScale =
Sheets("Chart").ChartObjects(1).Chart.Axes(xlValue).MinimumScale / 3
End Sub

(Please forget about the fact that the scale will be diminished every time
the combobox is changed. I just want to figure out how to be able to change
the minimumscale first.)

However, the following code can run:

Sub testaxis()
Sheets("Chart").ChartObjects(1).Chart.Axes(xlValue).MinimumScale =
Sheets("Chart").ChartObjects(1).Chart.Axes(xlValue).MinimumScale / 3
End Sub

That is: I can run the testaxis macro, but get an error of 1004 if I make
changes to the combobox and combobox1_change() is invoked.

Can anyone tell me what is wrong and what I should do?

Thanks.

Herbert

 

[Dave Peterson]

There's a bug in xl97 (fixed in xl2k) that deals with code called from controls
from the control toolbox toolbar that are used on a worksheet.

One fix is to change the .takefocusonclick property to false.

Another fix is to add:
activecell.activate
at the top of your code.

since the comboboxes don't have the .takefocusonclick property, you can use the
second suggestion.