Runtime error

[Anthony]

I have a .XLS document that runs macros correctly on my laptop with MS
Office 2007 but when I open it on my desktop with MS Office 2003,
gives me the following message: runtime error '438': object does not
support this property or method. I have enabled macros when I open
the workbook on the desktop. Any ideas how I can get the workbook's
macros to work correctly? By the way, the macro is assigned to a
button to create a new worksheet. If I click OK, the sheet is created
but all the data is not. Thanks.

 

[Dave Peterson]

Each version of excel added something that the old version didn't have.

I'm betting that one of the things that the macro does isn't supported in the
older version that you're running.

Depending on what the code is, you (or the developer) may be able to replace it
with equivalent code that will run on all (or at least most) versions of excel.

If the thing that the macro does is first supported in xl2007, then you're out
of luck in earlier versions.

I'm betting that it's a macro that's assigned to a button that sorts the data.
And it uses the new xl2007 syntax to do the sort. You may be able to just use
the older xl2003 (and below) version of the sort syntax and be happy.

If you're the developer of this workbook, it's usually much better to write your
code in the oldest version of excel that you have to support. Then test the
heck out of it in the newer version(s).

 

[Anthony]

Each version of excel added something that the old version didn't have.

I'm betting that one of the things that the macro does isn't supported inthe
older version that you're running.

Depending on what the code is, you (or the developer) may be able to replace it
with equivalent code that will run on all (or at least most) versions of excel.

If the thing that the macro does is first supported in xl2007, then you're out
of luck in earlier versions.

I'm betting that it's a macro that's assigned to a button that sorts the data.
And it uses the new xl2007 syntax to do the sort.  You may be able to just use
the older xl2003 (and below) version of the sort syntax and be happy.

If you're the developer of this workbook, it's usually much better to write your
code in the oldest version of excel that you have to support.  Then test the
heck out of it in the newer version(s).

When I debug the code, the following line is highlighted:
..ThemeColor = xlThemeColorDark1
Do you know why this would be giving me an issue and not allowing the
complete sheet to be created?

 

[Gord Dibben]

ThemeColor is new for Excel 2007.

See VBA help on "new members and constants"

which versions do not support.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Themes were added in xl2007.

I'm not sure how important the theme is to you. If it's not important, you
could ignore the error -- or even check to see the version of excel before
trying to use this.

On error resume next
'your themecolor line that errors
On error goto 0

Ignoring the error should only be used when you're positive that ignoring it
won't cause bad side effects. And it should only be used for as short a time as
possible (as few lines of code that you need).

You may decide that themes aren't important--but formatting is. You may be able
to replace the theme with old formatting code?????

 

[JLatham]

When that error comes up, click the [Debug] button. That will open the VB
Editor and highlight the line of code where the problem is taking place.

You could then post a copy of the code and tell us which line is the one
that got highlighted. Then people may be able to help you further.