chr error/compile error

[excellentexcel]

hi guys i ahev a piece of code that works on my system at home but i bring it to work and i get a compile error cant find project or library if anyone could help it would be great

this is the line it highlights

Please Login or Register  to view this content.

[Leith Ross]

Hello ExcellentExcel,

Whenever the string functions in VBA fail to load, it usually means Excel has become corrupted. Click here to view methods of correcting Excel problems.

[excellentexcel]

hi leith
how come it works at home but not at work?

[DonkeyOte]

Just out of curiosity if you go into the VB Editor & then to References -- what if anything listed as MISSING ?

[excellentexcel]

hi donkeyote
im currently at home now and its working fine
is there anything in particular i should be looking for?
kind regards

[DonkeyOte]

You will need to test at work where it's not working .... I was just curious ... if you're running different versions of XL @ home & work and home is newer it's possible that this is the issue... ie say you reference an Outlook Object Library in 2007 this would be 12.0 whereas in 2003 it would be 11... trying to run the VBA in 2003 environment would fail until the 12 library was replaced by version 11... the same holds true for 10 to 11 etc...

[excellentexcel]

hi donkeyote
both versions are 2007

[DonkeyOte]

Hmm... I'm not sure then... that's odd... I'm not sure the file can be corrupted if it runs on one machine and not another but would defer to others as it's not an area I have any expertise in ie Leith probably knows.

The only other culprit if both run same version would be a Calendar Control or something but I thought in 2007 that was automatically available (?) ... if you go to References in your home version what is listed as being active (checked) ?

[excellentexcel]

hi donkeyote
im at work now and i can see that microsoft calendar control is missing from my work account as opposed to home account.
how can i rectify this please
regards

[DonkeyOte]

You will obviously need to install the missing control (mscal.ocx)

You don't specify which version it is you're running at home (11?) ... I *believe* the calendar control forms part of the Professional version of Office (ie comes with MS Access) so if not listed on your work machine you will need to get hold of a copy of the appropriate control and install to your work PC (and possibly any other clients that may use the same file) - you may need to contact your IT dept for the relevant CD contents etc... (and you may well need local admin rights on the client to install)

(in case you run into further issues this thread may be of interest: http://www.mrexcel.com/forum/showthread.php?t=266690)

EDIT: I should of course add that if the file does not use the Calendar Control then simply uncheck it from the References list...

[excellentexcel]

hi donkeyote
ive not been able to get hold of our IT guys
and at home it says cant remove control or reference:in use
is there a way round this if i cant get hold of our IT guys?
kind regards

[Leith Ross]

Hello excellentexcel,

If you are running Excel 2003 you need to reference the Excel 2003 version of the Calendar control and if you are using Excel 2007 you need to reference the Excel 2007 Calendar control. For Excel 2003 the mscal.ocx file is located in this directory "C:\Program Files\Microsoft Office\Office11\". I don't know the path for Excel 2007, but you can use search to find it. You will need to have 2 workbooks. One with 2003 version of the control installed and the other with the 2007 version installed.

[excellentexcel]

hi leith
both versions are office 2007

[DonkeyOte]

The issue is as I've stated already that you need to install the missing control onto your work PC -- given the fact this is at work it is likely that whomever does the installation will require local admin rights on the client... I think in reality you will need IT to install this control for you but I reiterate every client that uses this file will need the control installed...

I suspect it would make a great deal more sense to dispense with the MS Active X control altogether and use a User Form approach, RoyUK has posted a utility to this effect which can be found here in the Development Forum, I would suggest you have a read through - it's "open source" so you can amend the code to meet your own requirements but he has done the hard work for you (ie building it)