Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Darkest Kent, UK., Kent, England
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to run when w/b opens (Excel '97)

    My brain has already left for the weekend.

    Please tell me what I need to call a macro to have it run automatically when I open a certain workbook.

    I thought it was Auto_Open, but nothing happens.

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

    Re: Macro to run when w/b opens (Excel '97)

    Auto_Open is OK ,but is there for backward compatibility (from xl5 days). It does not run when a workbook is opened using VBA code.

    Better is the Workbook_open event in the thisworkbook module.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro to run when w/b opens (Excel '97)

    In 97+ best to use the Private Sub Workbook_Open() event at the Workbook level. In the VBE Project Explorer window (left side), open the Microsoft Excel Objects for the workbook, double click the Workbook Object, in the code pane Object drop-down (upper left) select Workbook and (usually it will be created for you, but if not) from the Procedure drop-down (upper right) select Open. See also <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=75577>this thread</A>.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Darkest Kent, UK., Kent, England
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to run when w/b opens (Excel '97)

    Bingo! Thanks guys.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to run when w/b opens (Excel '97)

    Workbook_Open is the way to do this, but there is a risk associated with using it as it relies on events being enabled. In practice, you can't guarantee that another Workbook/Add-In has left events enabled.
    For this reason, I always put an Auto_Open into my Excel projects, make Workbook_Open public, and call Workbook_Open from the Auto_Open. I also make sure Workbook_Open sets a flag (Class scope Boolean) to prevent it from running twice.
    Some may consider this overkill, but in a situation where you really need to make sure your Workbook_Open code runs, and you have no control over other VBA that the users run in other Workbooks or Add-Ins then it is, unfortunately, necessary.

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to run when w/b opens (Excel '97)

    I like this idea as occasionally events have gotten disabled and I wasted too much time figuring out why my program isn't running. I've never used Auto_Open as I started VBA in xl97. Where does it go? I tried adding it in ThisWorkbook but it didn't run (yes it was public).
    <pre>public sub Auto_Open
    debug.print "in Auto_Open"
    end sub
    </pre>

    I then did the same debug.print statement in Workbook_open but when I then saved,closed, re-opened the WB I only saw the print message for WB open not Auto_Open. I then put Auto_Open in a Module but it didn't get fired first either.

    Deb <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro to run when w/b opens (Excel '97)

    Auto_Open() should be placed in a general module, e.g. Module1. If you have both the Workbook_Open event and Auto_Open coded, Workbook_Open runs first, but under normal circumstances both should execute.

    Andrew C

Posting Permissions

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