Macro Error

[hsk]

I have written a macro which pops up a user form. User form contains sreadsheet control where user provides certain inputs. My macro reads those inputs and processes further.

It is working parfect for me. I am using excel 2003. I sent this macro to my team members in different location. They are using office 2007. On running Macro throws error like ----

1. Can not find project or library
2. Pops up a msgbox for VBAProject password(users obviously do not have password)
3. Could not load object because it is not available on this machine
4. Compile error in the hidden module:Thisworkbook


Any body can help me on this?
How can I get this run? I can not go to user location to see exactly what's going wrong, nor can I share the password with users and ask them to debug the issue. Can I make some arrangement in code to find what is the cause of
"Can not find project or library" ....


Please help !!!!

 

[SydneyGeek]

On a 2007 machine go into the code (Alt+F11)
Tools | References. Look for anything marked as MISSING. Uncheck, then go down the list to find the latest available version of that library and select the check box.
OK, and you should be up and running.

Note: *Sometimes* you will be unable to choose the replacement library without closing the dialog and re-opening it. Something to do with refreshing the list

Denis

 

[Trebor76]

Hi hsk,

The error message is to do with the fact that the directory path for some of Excel 's program files changes between version releases causing their links to break.

To (hopefully) fix the problem, try these five steps on each machine running Excel 2007:

1. Open the file on the machine running 2007
2. Open the Visual Basic Editor (VBE) by pressing Alt + F11
3. From the Tools menu click References
4. Un-tick (de-select) any reference that says 'MISSING' from the References - VBAProject and then click OK
5. Exit the VBE by pressing Alt + Q

HTH

Robert

 

[hsk]

Thanks Denis ...

Can I put some code in my macro itself to this?
It sounds very simple and just one time activity but I really doubt users will be interested in doing it.
I have to automate it, is there any way to specify which all Lib's I am going to use ??

Thanks ..

 

[hsk]

Thanks Robert for your inputs ..

I am just searching for a way to do this automatically, Because this macro is going to n number of users. Every person may have different setup.

Any idea ?

 

[Trebor76]

Any idea ?

No, not by macro I'm afraid as programming to the VBE requires two key settings to be changed manually (see this detailed article by Chip Pearson for more info) which defeats the purpose.

The only thing I can suggest is that as well as having Excel 2003, you get Excel 2007 loaded on your PC, ensure your workbook works on that platform and then email the applicable version the the relevant people (or both versions if you're not sure who is running which version).

HTH

Robert