Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Workbook_Open in Personal.xls (2003)

    Hi,
    I wrote a Workbook_Open macro to create a custom menu and stored it in the ThisWorkbook module of my Personal.xls. I did this yesterday, and I've experienced some problems with Excel running macros from my Personal.xls since. Is this a practice to be avoided? I looked online and, while I didn't see any counsel against doing so, no one in any code examples that I saw did it, either.
    Thanks a lot.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open in Personal.xls (2003)

    I tend to put as little code in ThisWorkbook as I possibly can. For one: Editing in the VBA editor leaves behind all sorts of junk (you cannot see). Normal modules can be cleaned up by exporting, deleting and importing them. But the thisworkbook module cannot be removed; you must rebuild the workbook. Also, some code only works well in normal modules.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open in Personal.xls (2003)

    What problems are you having? What does the code do? Can you post a copy of the code?
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open in Personal.xls (2003)

    Hi Jan Karel,

    I am very interested in your remarks on this topic. Could you expand on them a little? For example, what code would work well in normal modules but not in workbook or worksheet modules?

    Thanks...

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open in Personal.xls (2003)

    Well, it's not exactly about particular types of code, but the thisworkbook module is a class module and as such behaves itself differently than a normal module.
    If you don't have the habit of fully qualifying your objects, code in thisworkbook sometimes doesn't work, where-as when you place the exact same cod ein a normal module it does.

    I have no examples at hand, because I'm speaking from experience I've had with other people asking questions in forums (like this one), which were solved by moving the code to a normal module.

    Another thing I often do in the Workbook_open event is NOT call the initialisation routines directly. Rather I use Application.Ontime to set an event that will run the init routine.
    The net effect is, that the init sub runs *after* Excel is fully loaded (including its addins).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open in Personal.xls (2003)

    Thanks I never thought about the permanent nature of thisworkbook in that way. I'll move the code elsewhere and call it. Never used application.ontime before, so I'll take a look at that.
    Regards

  7. #7
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open in Personal.xls (2003)

    HI Legare,
    The problems were:
    a) some macros in Personal.xls weren't being found when I ran them.
    [img]/forums/images/smilies/cool.gif[/img] a couple of crashes
    c) This morning, I sent a new workbook to someone, and suddenly she got one of my personal toolbars on her screen, although of course the macros it runs weren't present.
    (We've got a heavily protected environment. I ran a full scan anyway and came up with no viruses etc.)
    This last problem made me suspect that my xlb file was corrupt, so I deleted it. I also moved the code from thisworkbook in Personal per Jan. These two actions seem to have cleared up the problems.
    Thanks.

Posting Permissions

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