Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    autostart macro (xls 97)

    Any way to autostart a macro on the open or close of that file??? If yes what does it look like, thanks.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: autostart macro (xls 97)

    scrappe7

    Happy New Year. Yes Two ways:

    Old style: Name the subroutine <font color=red> Auto_Open() </font color=red> or <font color=red> Auto_Close() </font color=red> , these are special names that will run the code at the time when the workbook is opened.

    New style: Place the code to run in the <font color=red> Private Sub Workbook_Open() </font color=red> and <font color=red> Private Sub Workbook_BeforeClose(Cancel As Boolean) </font color=red>

    <font color=red> Remark: </font color=red> Try and keep the <font color=red> Auto_Open() </font color=red> routine to a minimum of 5 lines. Do something like:

    Sub Auto_Open()
    Call ContinueWithTheCode
    End Sub

    where ContinueWithTheCode will do the work. This is due to a bug in Excel 5.x and I am not sure if it was ever addressed.

    HTH

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autostart macro (xls 97)

    Hey wassim,

    I used auto_open but the coding that i ran has a close of that file automatically. How can i stop the auto_open from executing automatically on the open of the file. So that i can edit that file? I cant get to my coding in that file, i just need to pause it for edit

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: autostart macro (xls 97)

    scrappe7

    OK how are you opening the workbook that has the Auto_Open that you want to disable?

    If you are using Excel 97, you can use the Macro Virus tool, and say Do not Run Macros and that would be it. But make sure you have the VBE open first.

    You can hold the Shift Key down while opening the file.

    If you are opeing the workbook via code well as the OLH says [b] <font color=red> "If the workbook being opened has any Auto_Open macros in it, they won't be run when you open the file from Visual Basic." </font color=red> [b]

    HTH

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autostart macro (xls 97)

    Thank you wassim problem solved.

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: autostart macro (xls 97)

    Hi Wassim

    The Auto_Open bug you mention seems to have been fixed in Excel 97. I've been running about a 12+ line Auto_Open for several years without any signficant performance hits. Each line is a separate Call. The machine it was written on was a P166 w/ 80Mb RAM. (No issue in Excel 2000 either.)

    HTH
    Gre

Posting Permissions

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