Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts

    Assigning the 'On Open' macro ?

    I have some code in Private Sub Workbook_Open() which runs when the Workbook is opened.

    Sometimes I want to 're-initialise' the Workbook by running this code again. As the developer I can get to the code myself but cannot work out how to assign it to a button as it does not show in the list of assignable macros.

    I know that I could copy the code into another macro but, for reasons of maintenance and tidiness, would rather not have the same code in two places. This line of thinking led me to calling the Private Sub Workbook_Open() code from an assignable macro, but neither can I see how to call it !

    I suppose I could do this the other way around - park the code in an assignable macro and call that from Private Sub Workbook_Open().

    But am I missing something obvious ?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Martin,

    Try using this construct in a standard Module. It works for me and shows up in the Alt+F8 menu for running on demand.
    Code:
    Sub Auto_Open()
    
       NetWorthMenu
       
       If Application.Version = "14.0" Then SwitchTab "Add-Ins"
       
    End Sub                   'Auto_Open()
    Of course you don't need the two code lines replace with your own.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Perfect, thanks.

    This sort of knowledge is so hard to get by reading books !

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Martin,

    Glad to be of assistance.

    Don't discount the reading of books. You wouldn't believe the library of Excel and Access books I have with a few Word and PP thrown in for good measure. At approx $30 per book I figure if I get one tip/idea from a book the book has paid for itself! Granted that 80-90% of the information contained in say a given set of books on Excel is redundant it's that other 10-20% that can really pay off with the added benefit repetition drilling those redundant parts into my thick skull! On the other hand there is no teacher like failure. Or to put it another way Experience, let's just say I have a lot of Experience. And to finish it off it doesn't matter how much Experience a given biologic unit has it is finite and the world is infinite...thank heaven for the Lounge.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I always try by myself first because that's how, as you say, I learn - mostly.

    I do have books, and agree with you about their place - they are great for giving me the general idea about things. When I need a specific bit of information it can be hard to find, but the hardest is knowing when to look for things I don't know exist ! That is, as you say where the Lounge - specifically its contributors - is so helpful.

    Happy New Year to you.

    Martin

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FYI, I would put the code into a routine in a normal module and then call it (not duplicate it!) from the workbook_open event and wherever else you want it (my preference is not to call any code directly from the Open event but rather to use OnTime).

    If you do use the auto_open code you should be aware that it will not run automatically if you open the workbook in code - you have to use the Workbook's RunAutoMacro method for that.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks Rory.

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Just a side note: You can have only one Auto_Open routine in a project and I am not sure if holding down the shift key when the wokbook opens will bypass it from running.

Posting Permissions

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