I just copied the code as is on the web page and ran it in 2007. It
works exactly as advertised. When you get an error like "can't find
project or library", that means that one of the references in VBA is
broken. As often as not, the function that VBA says it can't find (in
your case, the "Mid" function) isn't in the library that is broken.
But since the compiler is choking on some variable or function, it
tells you that some reference is out of whack.

In VBA, go to the Tools menu and choose References. See if any
references are marked "MISSING". If a reference is missing, you have
three choices. If it is a primary library used natively by Excel/VBA
(e.g., VBA, Excel, Office, or OLE), you can typically fix it by
running Excel with the /regserver switch. Close all applications, then
go to the Windows Start menu, choose Run, and enter

"C:\Program Files\Microsoft Office\Office12\Excel.exe" /regserver

The full path name needs to be in quotes and there is a space between
the closing quote and the / character.

You may need to change the folder specification to point to where you
have Excel installed. The /regserver switch causes Excel to start, and
rewrite all of its registry keys and associations back to "factory
defaults". This can cure any number of ills. You may loose some
customization, but that is a minor issue compared to getting the
references back on track.

If the library marked MISSING is one that you don't need, uncheck it.
If it isn't referenced, VBA isn't going to care about it.

If the library marked MISSING is one that you do need, then you need
to re-install the program that created the reference. To determine
which program screwed things up, in VBA press CTRL G to display the
Immediate window, and there, type

?ThisWorkbook.VBProject.References(5).Description
and press Enter. Then type

?Thisworkbook.VBProject.References(5).FullPath
and press Enter.

Change the 5 to the position in the References list at which the
offending reference appears.

If that doesn't help you find what the problematic program is, type

?ThisWorkbook.VBProject.References(5).GUID
and press enter. Copy the GUID from the Immediate into the clipboard.
Then, from the Windows Start menu, choose Run, and enter

RegEdit.exe

In RegEdit, go to the Edit menu, choose Find, and paste in the GUID
that you copied from the Excel window. You can often track down
somewhat obfuscated or hidden program via their GUID in the Registry.
You may have to hit F3 a few times to find all occurrences of the GUID
in the registry. Note: Unless you really know what you are doing, do
NOT change anything in the Registry via RegEdit. Doing so can cause
problems ranging from minor annoyances all the way to not being able
to start the computer. Everything is RegEdit is "live". There is no
Undo or Close Without Save.

Based on the Description and directory path displayed in the Immediate
window, or from the GUID entries in the registry, you can probably
figure out what program is responsible for the library that is causing
the grief. Either un-install/re-install that program or contact the
vendor for an update.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sat, 29 Aug 2009 13:03:01 -0700, Horatio J. Bilge, Jr.
wrote:

I have a workbook which uses Chip Pearson's sheet visibility method of
ensuring that users enable macros (complete code at
http://www.cpearson.com/excel/EnableMacros.aspx).

The file works well in Excel2003 on my windows xp machine, but when I enable
macros in Excel2007 (running on Vista Home Premium), I get an error when the
macros run. A hidden module "modRequireMacros" contains two macros - one runs
when opening the file (UnHideSheets) and the other runs before closing
(SaveStateAndHide).

When the UnHideSheets macro runs, the error is "Compile error: Can't find
project or library" and "Mid" is highlighted in the following piece of code:
S = ThisWorkbook.Names(C_SHEETSTATE_NAME).RefersTo
S = Mid(S, 4, Len(S) - 4)

When the SaveStateAndHide macro runs, I get the same error and "Chr" is
highlighted in the following line of code:
ThisWorkbook.Names.Add Name:=C_SHEETSTATE_NAME, RefersTo:=Chr(39) & S,
Visible:=False