on 9/1/2011, aditel supposed :
Hi Garry,

thank you for your comments.
I will start with results of my checks and observations from your second
post. Unfortunatelly it is not enough to simly loop through the sheets of
another workbook, especially when it is opened from network folder. In such
cases CodeName is 'Empty'. That's why I decided to get to VBComponents via
VBProject.

I don't know what/how you're doing this but the worksheet loop code I
gave you ALWAYS returns the codename because ALL worksheets MUST HAVE A
CODENAME! Thus, CodeName will NEVER be 'Empty'! This works for ALL open
workbooks regardless of where they're stored/opened from.

CodeName is used by many developers to assign design time names to
multi-sheet projects/templates so a ref in our code can be acquired to
the needed sheet in case users rename their tabs. This is a reliable
methodology BECAUSE every sheet ALWAYS has a codename.

And now going to first post...
I changed data type to Variant and it worked! However when I saved project
and restarted Excel application it stopped working again. After several tries
it seems that it is not even necessary to change data type to Variant but
just open VBA editor and run the code. When you do this without opening VBA
in new Excel session it does not work.

I really have no idea how to solve this issue.

I ran YOUR code AS IS and VBA halted, highlighting the line 'dim
mybookProject As VBIDE.VBProject and stated 'User-defined type not
defined'. Changing VBC to Variant AND dropping mybookProject worked as
exampled.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc