Compile Error: Can't find project or library (1 computer has the error, another does not?)

I have a request form that has been used by two departments for the past two years. After we went through an upgrade from Office 2007 to Office 2010, Department A is still able to use the form, while Department B receives the message "Compile Error: Can't find project or library". I have attached to this post a copy of the form, and a screenshot of the error being received by Department B. The form itself is much more complicated, but this is a simplified version that saves the PDF to your desktop so that it can be tested by anyone. If I can fix the error on this one, I should be able to incorporate the solution into the more complicated version.

Thanks in advance for any help that comes my way!

Screenshot of the Compile Error >>> Compile Error.jpg

Please Login or Register  to view this content.

Hi jonvanwyk
The error you're receiving usually indicates a missing reference. I'd guess it's the Calendar Control as it's not included with Excel 2010. On the offending machine in VBA go to Tools ----> References and see if any are missing. If it is the Calendar Control you can download it from several sites. You may wish to explore options to Calendar Control...there are several good ones available.

Hi jonvanwyk

I'm unable to duplicate the error for either file in Excel 2007 or Excel 2010. I deleted the Reference to Calendar Control and both ran fine. Do you have ANY References on either machine that are marked MISSING?

Hello Dutch,

Check that the offending machine has VBA macros enabled and the Trust Centers are setup correctly for macros. The Left function is part of the VBA library. To check that this in fact the problem, prefix the Left with VBA (see the code below). If after the change, it errors at a different point, then it is confirmed that VBA library is not loading or loading incorrectly.

Please Login or Register  to view this content.

@Leith...Thanks for dropping in :-) I will check that this morning when the other department opens. Is it possible for all macros to be enabled, and the document to be trusted, and to still have issues with such a thing? If so, how does one fix that problem (and how likely would it be the the problem would exist on every machine in the other department)?

I should note that the actual code is a bit more complicated. Below is a sample of it in which the compile error first started with the [A50]. Someone recommended that I change the [A50] to "A50". When I did so, the form stopped working on the machines in our department. When used at the other department, it also did not work, but the error jumped from [A50] to the "Left(" error you see now.

Please Login or Register  to view this content.

I have attached the unadulterated form to this reply. It contains real email addresses in it, so I trust anyone looking at it will be careful in using it (though I am sure it wouldn't work for anyone else anyway given that the file name paths are specific to our network).

Records Request Form Excel 2003 Workbook.xls

PS.

Why do you use the variable filename ?
there's more robust code for that:

Please Login or Register  to view this content.

You can avoid the 'Left' issue.

To 'print' the PDF I think this will suffice:

Please Login or Register  to view this content.

I think you could create a new Excel document on the computer that produces the errors and copy your code into it.
It might work.
Although you may think you work with identical software, sometimes 'updates' can interfere.

The copying and pasting of the VBA is easy enough, but that means I have to create the spreadsheet all over again

I doubt that.

What happens if you replace [A50] by Range("A50")

Well, back to lesson 1 in VBA:

You can't use
If ("A50" = True) And

You can use
If Range("A50").value = True And

But you'd better use a reference to the sheet also:

Please Login or Register  to view this content.

And you'd better avoid sheetnames that can be mixed with other things in VBA, e.g. a Usrform with a name "Form1"
Caal them "Sheet_1", "Sheet_2", etc.

Thanks snb. I have made those changes (see attached file). I am going to test it over at the other department in a minute to see if that fixes the issue they were having. I get back to you.

The new and improved "Records Request Form Test C" works perfectly in our department, but gets the error below when used in the other department.

Please Login or Register  to view this content.

compileerror3.jpg

Further research suggests that the "MISSING: Microsoft Office Calendar Control 2007" on the offending computers might be the issue? The machines in our department (on which the form words perfectly) all have "Microsoft Calendar Control 9.0".

MISSING - Microsoft Calendar Control 2007.jpg

Hi jonvanwyk
YES!!! I believe I mentioned this previously.

You MAY be able to copy the Calendar Control from another machine...don't know. You CAN, however, download a copy and do the install. See this link and follow the instructions carefully.
http://www.excelforum.com/excel-prog...r-library.html

If you're distributing see this link for an alternative to Calendar Control
http://www.excelforum.com/excel-prog...r-control.html

You may try simply unchecking the missing control...see if your code runs...if not do the download.

Hello Dutch,

I have been doing a lot of reading as to why this problem maybe occurring. At this time, I haven't found any known issues with Excel 2003, 2007, or 2010 relating to this. The Environ method is also part of VBA. You can add the VBA prefix as before with Left. This should work. You can use CTRL+F to do a find and replace through out your project if there are several of these references in your code.

Strange thing is the brackets like [A1] did not change in either 2007 or 2010 from Excel 2003. Both versions support this short hand for the Evaluate method, which is part of the Excel application and not VBA. Perhaps it is related to the network setup. I'll keep looking.

Hi jonvanwyk

Did you clear the MISSING item in Tools--->References to see if this resolved the issue?

In the earliest versions of Excel 2007 the PDFwriter wasn't built in. You had to install that 'addin'.
The later versions contained the ThisWorkbook.ExportAsFixedFormat.

So if you are working in Excel 2007 and Excel 2010 the check whether the PDF addin has heen installed should be redundant.

If you skipped lesson 1 in VBA it might occur that the userform/sheet contains more controls than you are able to see. You could check that too.

Hi jonvanwyk

Did you clear the MISSING item in Tools--->References to see if this resolved the issue?

The form now works in our department, but not the other department. Everyone is on Office 2010 now.

So no use to check the existence of any addin to produce PDF's.

You made a refence to the VBA library by adding VBA. to the left side.
You could consider to add reference to the sheet you are referring to when adressing ranges.

Sheets("Sheet1").Range("A50").value is to be preferred instead of Range("A50").
You can use With ... End With to shorten your code (lesson 3 in VBA )

There's also only one VBA library....