VBA Codename Not Initialized for New Worksheets

Hi all,

I'm required to use a worksheet's codename to store its unique settings. (Excel name is useless since my clients can change it.)

Unfortunately, the CodeName property of a newly created worksheet are not initialized until the VBA Editor is opened at least one time (not good since not all of my clients are programmatically savvy) or a force recompile occurs. The CodeName is simply an empty string until one of those events occurs.

More information on this "feature" can be found here: http://www.office-archive.com/2-exce...925ebceecf.htm

I have found the following solutions on Google but both of them have issues:

1) Quickly display and hide the VBE. (I've tested this, and it just looks strange and a little unprofessional. However, if it's the least buggy solution, I have no choice.)

2) Programmatically force a recompile

Please Login or Register  to view this content.

Unfortunately, this block of code doesn't even compile on some setups - namely Excel 2007.

As a result, I'm hoping to get some feedback on other potential solutions on this CodeName property issue.

Perhaps, there's another way to force a re-compile?

Anways, thanks in advance.

OK. Will do.

All the best.

Originally Posted by shg

See, perhaps, http://www.siddharthrout.com/2013/04...that-you-know/.

Then you'd need to reprotect it.

Thanks for the link. It certainly looks promising and a useful reference for near-future projects.

I guess I'll discuss with a colleague to re-assess how our clients are using our product and then go from there. If necessary, maybe we'll create a shiny looking interface for them to manually unprotect their project.

Originally Posted by romperstomper

I haven't got 2007 to hand for testing but in 2010, this works:

Please Login or Register  to view this content.

Thanks for this answer as well.

Unfortunately, we ended up with a compiler issue on one of our XL 2007 test systems. I suppose an option would be wrap this block of code in a private function inside of a conditional compiling block. (#If... #Else... #End If)

I suppose half the fun of VBA is finding a workaround to MS's "undocumented features." Cheers all.