5

Is there anyway to have a userform that acts modeless, while still pausing code execution like a modal form?

I'd like the userform to show, but still allow interaction with the parent program. Modal forms block interaction with the parent program. A modeless form would work, but I would like the code execution to pause while the form is up.

I've worked around this by creating an infinite loop that checks if the form is visible, but that seems a bit hacky.

Public Sub GetFormInfoAndDoStuff    
  ufForm.show vbModeless

  Do while ufForm.Visible
    DoEvents
  Loop

  ' Do other stuff dependent on form 
End Sub

EDITED to clarify that code after .show exists which must execute after the user form is done

lfrandom
  • 1,013
  • 2
  • 10
  • 32
  • 1
    I understand that you want code to execute once the UF is closed, I am merely suggesting that the method you are using to approach this is wrong. Put the executable code in another module, and call it from the `UserForm_Terminate event.` – David Zemens May 31 '13 at 19:33

3 Answers3

6

You should be able display the form as vbModeless and only execute code when specifically requested, i.e., from a CommandButton or other control.

You then leave the form visible/shown until it is specifically closed, via the "X" button or via another control which calls the UserForm_Terminate event.

In order to achieve this, you may need to move some of your executable code in to another subroutine and/or module, and call this subroutine for example from a CommandButton_Click event.

You already have a subroutine somewhere that contains a line like:

Sub ShowTheForm()

    UserForm1.Show vbModeless
End Sub

So the form is displayed properly to allow user-input to the parent application.

You don't really need to put any other code in the above module. We will put the other code in other modules/subs, and then call it from user controls like command buttons.

Example:

Take all of your executable code, and put it in another subroutine (and if it suits your organizational preference, another module), like:

Sub MyMacro(msg$)
    MsgBox msg
End Sub

On the UserForm, add a command button and assign it the following code:

Sub CommandButton1_Click()
    MyMacro "hello"
End Sub

Now, the form will display until the user clicks the "X" button. Code will only run when called from the command button.

EDIT FOR CLARIFICATION

You don't need to "pause" the execution using this method. Execution ends once the form is displayed modelessly, and the form persists. The object has some events which you may use to trigger further execution of code.

David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • Unless I am reading you incorrectly, this works, but only in the case where the main module doesn't have anything else to do. In the main module, I am doing a lot of activities within my main module, after the form is displayed. If I just use a modeless form, the code below the .show executes right after showing the form. – lfrandom May 31 '13 at 15:46
  • I think you need to reoganize your code. I currently have an application with about 50 code modules, several hundred subroutines and functions, 6 user forms with hundreds of form fields spread across those forms, and a class module for Events. Everything in my application is dependent on the form **as inputs** to execute other subroutines and functions. The philosophy is to let the form be a form, and use other subroutines and functions to perform operations on your workbook, etc. – David Zemens May 31 '13 at 16:22
  • **If I just use a modeless form, the code below the .show executes right after showing the form.** This is precisely why you need to send your *executable* code in to other modules and subroutines, and only call those subroutines when appropriate. – David Zemens May 31 '13 at 16:22
  • You could even call your executable code from the `UserForm_Terminate` event handler. – David Zemens May 31 '13 at 16:23
  • I get what you are saying, but from an flow standpoint, I need the code in my main module. Basically the user form allows the user to choose an application, but in some cases we just give them a new application. The main looks like If not NewPerson then Set Application = GetPersonApplication where GetPersonApplication is this form. – lfrandom May 31 '13 at 19:29
  • I'm pretty sure there is no *need* for the code to all be in the Main module. You can always create public variables, or pass Object variables between subroutines and modules, etc. – David Zemens May 31 '13 at 19:35
1

Here's what I do.

This example is for a form I called "Find Header". The code tries to find several column headers, but the markers for a few of them may be missing (and the header text may have been overwritten with something random), so I may need to pause and ask the user to locate (click on) some of the headers for me:

First, put this declaration in a standard module:

Public bDlgFindHeaderIsShowingModeless As Boolean

Then, put this in the event procedure for any button or other control that dismisses the modeless dialog, such as the Click events for the form's OK and Cancel buttons:

bDlgFindHeaderIsShowingModeless = False

Then, put this wherever in your code you want to show the modeless form while paused for user interactivity:

bDlgFindHeaderIsShowingModeless = True 'init
frmFindHeader.Show vbModeless
Do
    If Not bDlgFindHeaderIsShowingModeless Then Exit Do
    DoEvents
Loop

Yes, it churns the CPU, so you might not want to do it if you're on a single-core processor and there are critically important background processes running. But it works; the user is able to easily and smoothly interact with Excel while the modeless form displays. The user doesn't feel like they are fighting an endless loop.

Greg Lovern
  • 958
  • 4
  • 18
  • 36
0

The Best method would be to use two different subs. I was able to solve this problem without splitting my sub as follows:

Public Mode as Boolean

Sub Stuff()
    If Mode Then
        Goto Continue
    End If

    'Code before Userform

    Mode = True
    Userform.Show vbModeless
    Exit Sub

Continue:
    Mode = False

   'Rest of your code

End Sub

I made "Mode" a global variable because I use this userform for multiple subs. If you are using a single sub you can use it locally. I also made "Mode" false when opening this workbook by going under "ThisWorkbook" Tab and adding the following code:

Private Sub Workbook_Open()
    Mode = False
End Sub

This again will only be needed if you use your userform for more than one sub. Last add this code under your Userform code when your proceed button is pressed.

Private Sub Confirm_Click()
    Userform.hide
    if Mode Then
        Call Stuff
    End If 
End Sub

If you are only are using the one sub method skip the if statement and just call the sub.

Nate
  • 1