Results 1 to 8 of 8
  1. #1
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MsgBox (2000/2002)

    Is it possible to create a self-closing MsgBox that (for example) pops up while a lengthy macro runs, then closes on completion without user intervention?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MsgBox (2000/2002)

    A message box is modal, i.e. execution of code is halted until the user closes it.

    Instead, you can create a userform with the appropriate message in a label. In Excel 2000 and up, a userform can be opened modeless (non-modal), so that execution of code will continue:

    ' show message form
    frmMessage.Show False ' false means modeless
    ' call lengthy macro here
    ...
    ' close message form
    Unload frmMessage

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MsgBox (2000/2002)

    Leif,

    Another way you can approach this is to place a textbox on the active sheet with your desired message and programmatically set visible to false. When your macro runs you call the routine which makes it visible for a specified period of time.


    To hide textbox and to show for modification of the textbox (message, location, size):
    <pre>Sub HideTextBox()
    Sheets("Sheet1").Select
    ActiveSheet.TextBoxes.Visible = False
    End Sub
    </pre>

    <pre>Sub ShowTextBox()
    Sheets("Sheet1").Select
    ActiveSheet.TextBoxes.Visible = True
    End Sub
    </pre>


    This is what would be called from your macro:
    <pre>Sub DisplayMessage()
    Dim sStartTime As Single

    With Sheets("Sheet1")
    sStartTime = Timer
    If sStartTime > 83697 Then
    sStartTime = 0
    End If

    ActiveSheet.TextBoxes.Visible = True
    Do While (Timer - sStartTime) < 4
    DoEvents
    Loop
    ActiveSheet.TextBoxes.Visible = False
    End With
    End Sub
    </pre>


    edited to correct my grammer and spelling

  4. #4
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MsgBox (2000/2002)

    Nearly there!
    The form comes up OK and if I step through the macro, no problem.
    However, if the macro runs as planned the (gray) message area comes up white!

  5. #5
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MsgBox (2000/2002)

    Neat idea which I may yet use - thanks!
    I'd like to crack the userform one first if I can, as I can see it being more useful in the future.

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

    Re: MsgBox (2000/2002)

    Have you tried setting Application.ScreenUpdating=False while the lengthy macro runs? HTH
    Gre

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MsgBox (2000/2002)

    Leif, try forcing the userform to be drawn correctly by inserting a statement UserForm1.Repaint (substitute the correct name of the form) after showing it, but before calling the macro.

  8. #8
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MsgBox (2000/2002)

    Bingo!

    UserForm1.Show False
    UserForm1.Repaint
    ....MyMacro
    UserForm1.Hide

    - does the job.

    Thanks all.

Posting Permissions

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