+ Reply to Thread
Results 1 to 4 of 4

Return focus to worksheet while mgsbox being used?

  1. #1
    Registered User
    Join Date
    10-03-2003
    Posts
    2

    Return focus to worksheet while mgsbox being used?

    While you have a MsgBox window displayed, is it possible to somehow return focus to the user to do something on a worksheet?

    For instance, I essentially want to pause in mid script, allow the user to do something on the worksheet, and then continue the script.

    How can that be done???

    Thanks

    -Jeff

  2. #2
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile

    Good evening Jeff

    No you cannot - a msgbox always requires an input from the user to terminate.

    However, you could design a userform and either, dismiss it to allow a user to do something before the macro carries on, or if you are using XL 2000 or later call the dialog box as modeless so that a user can work on the worksheet while the dialog is still on screen. Once you have designed your dialog box the code you would need is:

    UserForm1.Show
    or
    UserForm1.Show vbModeless

    and to close:

    UserForm1.Hide

    HTH

    DominicB

  3. #3
    Registered User
    Join Date
    10-03-2003
    Posts
    2
    I was able to design my userform and invoke it, but the script just displays the userform window and then breezes right past it. Do I need some kind of loop in the userform to not return control back to the script until it is dismissed?

    Right now, the only code I have in the user form is:
    Private Sub CloseButton_Click()
    Unload Me
    End Sub

    Thanks.

  4. #4
    Forum Contributor
    Join Date
    01-21-2005
    Location
    Colorado
    MS-Off Ver
    2000,2003,2007
    Posts
    481

    Return focus to worksheet while msgbox being used?

    Jeff,
    With a modeless userform you are right and it returns focus to Excel and continues running the rest of the code. One way to get around this is to break up your code into two separate sub routines. One to run before and up to showing your userform, and the other to run after your userform is closed.

    Sub FirstHalf
    'Your code here
    UserForm.Show
    End Sub

    Sub SecondHalf
    'More code here
    End Sub


    Then for the click event for your close button put a call to your SecondHalf sub

    Private Sub CloseButton_Click()
    Unload Me
    SecondHalf
    End Sub

    I'm not sure if there is another solution to your problem but this works well.

    Hope this helps

    B

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1