+ Reply to Thread
Results 1 to 11 of 11

VBA Codename Not Initialized for New Worksheets

  1. #1
    Registered User
    Join Date
    11-10-2014
    Location
    WindowsLand and LinuxLand
    MS-Off Ver
    2010
    Posts
    5

    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.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,637

    Re: VBA Codename Not Initialized for New Worksheets

    I don't understand the question. This works fine for me:

    Please Login or Register  to view this content.
    EDIT: I retract that.
    Last edited by shg; 11-10-2014 at 07:35 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,637

    Re: VBA Codename Not Initialized for New Worksheets

    Derivative to Tom Ogilvy's suggestion,

    Please Login or Register  to view this content.
    Last edited by shg; 11-10-2014 at 08:46 PM.

  4. #4
    Registered User
    Join Date
    11-10-2014
    Location
    WindowsLand and LinuxLand
    MS-Off Ver
    2010
    Posts
    5

    Re: VBA Codename Not Initialized for New Worksheets

    Thanks so much shg (& Tom Ogilvy).

    Any charities/causes you'd like me to support/donate to?

    Your code produces the actual codename - even for newly created worksheets! I still have no idea why we need this workaround in the first place, but I'm just thankful for this response.

    I've also slightly modified the code to be able to retrieve the .CodeName of (Newly Created) worksheets from other workbooks as well.

    Please Login or Register  to view this content.
    Cheers & thanks,
    ILVJRP
    Last edited by ILuvVBAJavaRubyPHP; 11-10-2014 at 08:42 PM.

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,637

    Re: VBA Codename Not Initialized for New Worksheets

    Any charities/causes you'd like me to support/donate to?
    http://cdlsusa.org/ , thank you.

  6. #6
    Registered User
    Join Date
    11-10-2014
    Location
    WindowsLand and LinuxLand
    MS-Off Ver
    2010
    Posts
    5

    Re: VBA Codename Not Initialized for New Worksheets

    OK. Will do.

    All the best.

  7. #7
    Registered User
    Join Date
    11-10-2014
    Location
    WindowsLand and LinuxLand
    MS-Off Ver
    2010
    Posts
    5

    Re: VBA Codename Not Initialized for New Worksheets

    I just did some more testing and ran into a snag:

    The function won't work if a workbook's VBProject is protected. As a result, any newly created worksheet will still have the empty String as a CodeName.

    Even showing then hiding the VB Editor won't initialize the CodeName property of a newly created worksheet (if the VBProject is protected).

    For reference, the code that I'm using is below:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.


    I don't know if anyone has a workaround for this issue? Or maybe there's another property other than .CodeName to uniquely identify a worksheet?
    Last edited by ILuvVBAJavaRubyPHP; 11-11-2014 at 05:24 PM.

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,637

    Re: VBA Codename Not Initialized for New Worksheets

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

    Then you'd need to reprotect it.

  9. #9
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: VBA Codename Not Initialized for New Worksheets

    I haven't got 2007 to hand for testing but in 2010, this works:
    Please Login or Register  to view this content.
    Remember what the dormouse said
    Feed your head

  10. #10
    Registered User
    Join Date
    11-10-2014
    Location
    WindowsLand and LinuxLand
    MS-Off Ver
    2010
    Posts
    5

    Re: VBA Codename Not Initialized for New Worksheets

    Quote Originally Posted by shg View Post
    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.

    Quote Originally Posted by romperstomper View Post
    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.

  11. #11
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: VBA Codename Not Initialized for New Worksheets

    What was the actual compiler error?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Error 92: For loop not initialized...how to fix?
    By excelspud in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-01-2011, 09:55 PM
  2. Excel 2007 : How to re-initialized column
    By learning_excel007 in forum Excel General
    Replies: 2
    Last Post: 04-15-2010, 10:53 PM
  3. [SOLVED] How does Userform get initialized by this?
    By RB Smissaert in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-12-2005, 01:50 PM
  4. Form Initialized
    By ceemo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-30-2005, 02:37 AM
  5. Help: UDF Used in Conditional Format not Initialized
    By Dan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2005, 12:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1