Results 1 to 5 of 5

Thread: autorun

  1. #1
    wheels
    Guest

    autorun

    I want to automatically run a macro on opening a new worksheet from a template.The macro is inserting a number and altering the format of the date.I believe it is possible but have been unable to find anything in the help files to assist.
    Thank you. I am using excell 97 sp1

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

    Re: autorun

    If you place your code in the Workbook Open event it will run each time the file is opened. <pre>Private Sub Workbook_Open()
    your code here
    End Sub</pre>

    This code must be placed in the ThisWorkbook object and not in a general module. You can do this from the VBA editor by double clicking on the ThisWorkbook of the VBAProject you want to work with. Then at the top of the main editing window you will see two dropdown buttons. Click on the one that reads General and select Workbook and you should be ready to go.

    Andrew C

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: autorun

    Andrew -- That is the correct solution, but I think Microsoft got it wrong to begin with. How long has DOS been around, with it's Autoexec? Us old Lotus users could make a macro and have it run automatically. (OK, it wasn't actually called Autoexec, but the concept was the same.) Microsoft even got it right with Access when they included Autoexec as a special macro name.

    So why can't Microsoft do the logical thing with Excel? <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    When I was looking to create a macro that would run automatically whenever the worksheet was opened, my first thought was to try naming it "Autoexec" out of long-established habit. When this didn't work, over the course of a few days I spent hours digging through Excel's Help, and then the Knowledge Base, but Microsoft did not have "Autoexec" anywhere in the help file or KB -- not even as a cross reference leading to "Workbook_Open()". <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

    I concede that writing macros is going a step or few beyond basic worksheet programming, but I think forcing users to learn VBA for something so basic as Autoexec is a little too much. Especially when Microsoft makes it so easy to actually records macros.

    Apologies for this little rant; you'll now be returned to your regularly scheduled program. <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

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

    Re: autorun

    No need to apologise for getting something off your chest - you made some good points, especially the lack of a cross reference to Autoexec. Workbook_Open only arrived in XL97, prior to that Auto_Open was the method. You still can place a macro Call Sub Auto_Open in a general module and it will run when the workbook is opened. There is also a similar Auto_Close.

    Andrew

  5. #5
    wheels
    Guest

    Re: autorun

    Thanks Andrew.
    I had tried along the lines of autoexe as well and also spents hours in the help filess with no luck. Thanks again for the info.

Posting Permissions

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