Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    add delay in macro (excel97+)

    During the running of a large macro I open a file named c:whatever.xls
    The next step is to select a certain range of the newly opened file for copying however Excel generates an error due to the fact that Excel wants to select cells before the new called for file is fully open. Is there a way to create a "pause/delay/ wait ???" before calling the select range in the newly opened file?
    Thanx
    Smbs

  2. #2
    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: add delay in macro (excel97+)

    You should post this to the VBA forum but I'll answer it here. I can think of a few solutions so take your pick...

    1. create global flag/boolean which is set to true when the other file is completed opened (it is set to TRUE in the workbook_open event of this file). The 'waiting' script loops until this flag is TRUE and then continues with what it needs to do once it knows this file is open.
    <pre>Public WaitForFile as Boolean

    private sub workbook_open()
    WaitForFile = True
    end sub

    ' In your other code do this...
    while not WaitForFile
    ' do nothing
    wend

    ' now continue with your code since the other workbook is open
    </pre>


    2. You can use Application.Wait to wait for a specific time but that's kind of dangerous since you never know exactly how long to wait (many variables can affect how long it takes to open).

    HTH,
    Deb <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

Posting Permissions

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