Excel 2013 VBA Macro Spinbutton code broken since December Update

Question

Hello All,

I have a complex WorkBook which uses many macros. One of them changes a chart's axis min/max via a SpinButton. This scripts works in Excel 2007/2010 and 2013 up until the recent December update. It doesn't work anymore. The spin button points to the following typical code on the same sheet as the button:

Option Explicit

Dim aY, bY As Single ' Y Max & Min

Private Sub SpinButton1_Change()

aY = Sgn(SpinButton1.Value) * (0.1 * Abs(SpinButton1.Value)) ^ 2.3 * 1.03

With ActiveSheet.ChartObjects("Chart_s1").Chart

.Axes(xlValue, xlSecondary).MaximumScale = aY

End With

End Sub

Since the last update clicking on the spin button has no effect. Also when you view the spin button's properties, the min/max values are missing (but are still there in Excel 2007).

Another macro which modifies chart axes aborts with Run time error 438 "Object doesn't support this property or method" with

ActiveSheet.SpinButton1.Value = 100    being the problem code.

It seems that the latest update has broken something in VBA? Can anyone shed some light on this please?

Thank you

Answer 1

Hi,

You may have a new update waiting to install that should fix the issue. If not then follow the steps in the thread below described by Jan Karel Pieterse

http://answers.microsoft.com/en-us/office/forum/office\_2010-excel/cannot-object-insert-active-x-control/203d3314-06ce-41b7-9f7d-c995b9b0db44

Answer 2

Hi,

The Excel development team have now responded.

http://blogs.technet.com/b/the\_microsoft\_excel\_support\_team\_blog/archive/2014/12/11/forms-controls-stop-working-after-december-2014-updates-.aspx

Answer 3

Hi,

Just a heads up regarding a potential problem with Jan Karel Pieterse's workaround. It seems to fix my WB while working in Excel 2013. When I use the same file in Excel 2010 on my work PC VBA is once again broken (In the past it worked).

There's a possibility that 2013 is messing up my .xlsb file.

Thanks

Answer 4

Thanks Mike, it did the trick! My Sunday is once again livable! :-)