Problem with xl Constants in Excel 2000 vs Excel 97?

[Chuck Ritzke]

Hi,

I'm stuck on a problem that appears to be due to some difference between
Excel97 and Excel2000. I have an Excel sheet with VBA that includes the
following lines of code....

Range("B11").Interior.ColorIndex = xlNone '= -4142
Application.Calculation = xlCalculationManual '= -4135
Application.Calculation = xlCalculationAutomatic '= -4105

These are the only three lines where I am using xl constants. They work as
desired in Excel 2000, but my client has Excel 97 and gets a 1004 error on
each of these lines (not able to set ColorIndex, etc). If I remove these
lines, everything works on his machine.

I first assumed that it might be that the xl constants have changed since
97, so I gave him a version where I used the actual numbers. But that still
didn't work.

I also walked thru debug with him on the phone and those constants do show
up in View-Object Browser with these same values, so they seem to exist on
his setup. I also looked online at Excel97 documentation and it appears to
me that those constants should work in 97 or 2000.

I had him check the references with in his Excel97 VBA screen and they are
the same as mine, except Excel 8.0 vs Excel 9.0.

Then I thought perhaps it was just a coincidence and that there was some
other problem with the lines, but I also have the line...

Range("B11").Interior.ColorIndex = 14

....and that one has no problems.

I could probably solve this if I had access to Excel97, but I don't
currently.

Any ideas?

TIA,
Chuck

 

[Jan Karel Pieterse]

Hi Chuck,

These are the only three lines where I am using xl constants. They work as
desired in Excel 2000, but my client has Excel 97 and gets a 1004 error on
each of these lines (not able to set ColorIndex, etc). If I remove these
lines, everything works on his machine.

I assume this code is called by clicking a control toolbox button.

Setting its takefocusonclick property to false should fix it.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Chuck Ritzke]

Thanks very much. That did the trick.

Hi Chuck,



I assume this code is called by clicking a control toolbox button.

Setting its takefocusonclick property to false should fix it.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com