Excel - Run-time error 40036 Application defined or object defined error

Asked By Joh on 21-Aug-09 05:14 AM
Hi,

I have had some suggestions back on this topic (thanks Joel) but nothing still
seems to work.  Out of about 100 users who use the XL app, 8 have this
problem.  We're running XL2002 SP3 on Windows XP.

I am completely lost as to  what the problem is.  I am convinced its not the
Excel application i use simply as it works fine on the majority of users
desktops.

I would appreciate any ideas at all on this.
Thanks
John




OssieMa replied on 21-Aug-09 07:17 AM
Hi John,

If re-posting an unsolved problem, it is a good idea to list the things you
have already tried so you are not getting repeats of the same answers that
did not work before.

However, what happens if you manually compile the code? That is VBA menu
item Debug -> Compile. If Compile is disabled because it appears to be
already compiled, make a dummy change to a line of code like change a
character then change it back again so you can then run Compile.

Some users do not select complete installation when installing Office so
should check that also.

--
Regards,

OssieMac
Joh replied on 21-Aug-09 07:45 AM
Hi,
Thanks for the suggestion.  Sofar have re-installed XL on the users machine.
No joy.  I thought it might be the References as we have XL02 SP3 and one of
the References is referring to SP6 (MS Windows Common controls 6.0)  But this
is also the same on every machine and its only a half dozen or so that are
affected.

I have read some webpages and a possibility could be the Registry (an area i
avoid like the plague!!) that, and i would need to the tech. support guys to
check into this one for me as i dont have Admin rights.

I dont know if trying your idea would make a difference seeing as the VB
works fine on most machines.  I dont even know if its worth upgrading to
XL03. I am open to anything at the moment.
Rick Rothstein replied on 21-Aug-09 10:44 AM
I am not sure what you mean by "this is also the same on every machine"... do
you mean the References on those computers giving you trouble show a valid
reference to Microsoft Windows Common Controls 6.0? I ask because, as far as
I know, that control set is not installed natively with any version of
Windows. I am pretty sure it comes from either installing the *compiler*
version of VB (this is the program development language, not VBA) or from
installing an program that was developed with the compiler version of VB
(there are hybrid commercial programs out there that use, for example VB6
and C++) and which makes use of a control from that control set. If your
user's computers that cannot run your program do not have the Microsoft
Windows Common Controls 6.0 control set installed and registered on their
computer from one of the two methods I mentioned, then they will not be able
to run the part of your code that references one or more controls from that
control set.

--
Rick (MVP - Excel)
Joh replied on 21-Aug-09 12:06 PM
Hi Rick,

Thanks for the insight.  For clarification, the References on each computer
are the same yet with 7 users out of almost a 100 i get this error.  We all
run the same version of Excel.

I have got the Tech.  support team looking at it as i'm convinced the problem
is not with the VBA code.

REgards.
John
OssieMa replied on 21-Aug-09 05:07 PM
Hello again John,

If you have not already done so then try my suggestion anyway; particularly
now in view of the reference you mention in later posts.

I picked up this idea from cyberspace when I had a problem that was not
exactly like but not totally unlike what you are experiencing. (Works on some
machines and not others). Compiling the code on an errant machine highlighted
the line that was giving the problem and pointed me in the right direction.

I did not explain this properly in my earlier post when I mentioned an
incomplete installation of Office but re-installing Excel on its own did not
fix my problem. While at the time it was only a long shot when I tried it, I
completely uninstalled Office then did a re-installation of Office, selecting
the Complete installation option, then downloading all of the updates. My
thoughts here are that a complete installation of Office installs something
else that gets used by VBA in Excel and it is not installed with a re-install
of Excel.

--
Regards,

OssieMac