Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    At the end of a long macro I have inserted a message box in an attempt to alert the user when the macro ends, with the standard "ding" sound that message boxes make when appearing, and of course with the appearance of the message box itself.

    The relevant part of my code is shown below.

    The problem is that when the macro is almost over, the message box does not appear automatically; rather, when the message box is supposed to appear, Excel's title bar flashes (silently) until the user clicks somewhere on the screen; only then does the message box appear (with a "ding"). How can I make the message box simply appear without requiring the user to notice the flashing title bar, and to know to click the screen?



    ----------------
    [relevant part of my buggy code]

    'all prior code, and then ....

    Application.ScreenUpdating = True


    MsgBox "The macro has finished."


    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I often use a message box at the end of a lengthy macro, and it appears automatically.
    Do you set focus to other applications during the course of the macro?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='769028' date='03-Apr-2009 14:06']I often use a message box at the end of a lengthy macro, and it appears automatically.
    Do you set focus to other applications during the course of the macro?[/quote]



    No, not that I'm aware of at least. The macro hops around the workbook, but shortly before the beginning I sent, I have the macro select a cell on the worksheet of interest (before turning screen updating back on)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Selecting a cell shouldn't affect the behavior of a message box. Could you attach a small sample workbook that demonstrates this behavior?

  5. #5
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    Unfortunately the macro and what it does is embedded deep within a large, complex workbook... actually two workbooks. I could attache them a s a zipped file but I doubt you want to go through all the code....

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could create a copy of the workbook(s) and successively remove bits of code until the problem disappears. That might give you an idea of where it arises.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='769034' date='03-Apr-2009 14:34']You could create a copy of the workbook(s) and successively remove bits of code until the problem disappears. That might give you an idea of where it arises.[/quote]


    Cheers

  8. #8
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    The problem has mysteriously resolved itself

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='lingyai' post='769060' date='03-Apr-2009 16:14']The problem has mysteriously resolved itself[/quote]
    Great!

Posting Permissions

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