ResetColors failed

  • Thread starter Thread starter Sandy V
  • Start date Start date
S

Sandy V

Hi

From an XL97 WB I am trying unsuccessfully to reset the
colors in an Excel5-7 file:

ActiveWorkbook.ResetColors

Error 1004 Method 'ResetColors' of object'_Workbook' failed

If I do same manually it works fine, a recorded a macro
also produces same line as above (but no error).

I think it must be something peculiar to the file I am
trying to reset, I can reset other xl 5-7 files without
any problem. The file has no protection of any kind, at
least none I can identify.

TIA for advice
Regards,
Sandy
 
Dave,

Thanks for your reply, as ever with detailed explanation.

It is not relevant how the macro is called, same problem
even from a copy of a recorded macro from the VBE.

It seems that the "something peculiar about this file" is
the mere fact it contains a code module. Remove all
modules and "ActiveWorkbook.ResetColors" works fine.

I've found some more old XL 5-7 files from different
sources, and experience the same with these.

It's not the most pressing issue, however I would be
interested to know:
A. how to workaround the problem without removing modules
B. if it's possible to replicate the problem in later files

Thanks and regards,
Sandy
-----Original Message-----
How do you run this macro? Do you run it from a control from the control
toolbox toolbar that's placed on a worksheet?

If yes, there was a problem with xl97 (fixed in xl2k) that caused a 1004 error
(but I don't have xl97 anymore to test to see if this is really the problem,
though.)

If you're running from that controltoolbox toolbar control, try changing the
control's .takefocusonclick to false. (or
Click to expand...
add "activecell.activate" to the top
 
I just suggested a macro called ResetColors (already went back and said not to
use this name).

Maybe you have an existing macro (or range name?) using it.
 
I got nothing!

Sorry

Sandy said:
Dave

Thanks again. I appreciate you comments, however I'm sure
there has never been a macro named "ResetColors" in any of
the XL5-7 files I've tested, nor have I ever named any
macro "ResetColors", nor used any duplicate macro names in
respective files.

I tried some more including some '95 files from Stephen
Bullen (always worth a look).
http://www.bmsltd.co.uk/Excel/SBXLPage.asp
eg checkkey.zip

I cannot apply ActiveWorkbook.ResetColors to any of these
which contain code, although manually no problem.
Same results XL97 & XL 2000.

A bit of a mystery!

Regards,
Sandy
Click to expand...

<<snipped>>
 
Back
Top