Return from Modal Form without Hiding the Form 1

[DaveInIowa]

Is there any way to return from a modal form without using Me.Hide? I would like to show a modal form and wait for input. Afterwards, I extract values from the form via user-defined properties (one of which tells me whether the OK or Cancel button was clicked). I then use these properties as parameter values to a longer running subroutine. What I'd like to do is to keep the form visible with the OK button disabled and the Cancel button still active for the user to set a global boolean to stop the longer running sub.

I've tried displaying the form (modally), then using a user-defined property (.Mode in the code below) to disable the OK button, then displaying the form again as modeless. This works but will not redisplay the form in its same location if it has been moved. I don't want to go to the trouble of saving its coordinates to manually set its position for the redisplay.

I realize I could call my subroutine from within the form itself, but this type of coupling is what I'm trying to avoid. I don't want to tie a form to a specific procedure.

Public Sub PromptRefresh()

    frmPrompt.Mode = Prompting
    frmPrompt.Show
    If (frmPrompt.DialogResult = vbOK) Then
        frmPrompt.Mode = Executing
        frmPrompt.Show vbModeless
        DoEvents
        Call ExecuteRefresh(frmPrompt.WhereClause)
    End If
    Unload frmPrompt
    
End Sub

 

[ettienne]

What about another modeless form with just a cancel button on it?
It's not clear if you want to specifically keep your form open, or if you just need a cancel button somewhere for the user to click.

 

[DaveInIowa]

Thanks for taking the time to reply. I'd like to keep the same form open since it will still contain the values keyed by the user. Also, if I used another modeless form, I'd still have the behavior described above of it not being positioned properly without a lot of extra work.

 

[combo]

You can still use Hide/Show to change the mode. The change of place where the form is displayed is caused by StartUpPosition setting. The default (1) is CenterOwner, either set it to 0 (manual) and set position when you show the form or change it in runtime:

With Me
    .StartUpPosition = 0
    .Hide
    .Show False
End With

combo

 

[combo]

.. and alternative solutions:
- with API call:

Private Declare Function EnableWindow Lib "user32" (ByVal hWnd As Long, ByVal lEnable As Long) As Long
Private bFormModal as Boolean

Private Sub SetModal()
EnableWindow Application.hWnd, bModal + 1
End Sub

or
- use Application.Interactive, it's more risky and requires cursor adjustment.


combo

 

[DaveInIowa]

On the initial display of the form, I want the form to be CenterScreen. Then, if the user drags the form to another location before clicking OK, I want the form to appear in the same location it was just dragged. I don't see any other way of doing that other than saving the current location before hiding the form, changing the StartUpPosition to Manual and then restoring these parameters when it is next activated. Here's a sample of the code I ended up with in case anyone is interested.

Option Explicit

Public Enum FormMode
    Prompting
    Executing
End Enum

Private mDialogResult As Integer
Private mMode As FormMode

Private mStartUpPosition As Integer
Private mTop As Single
Private mLeft As Single

Public Property Get DialogResult() As Integer
    DialogResult = mDialogResult
End Property

Public Property Get WhereClause() As String
    WhereClause = txtWhereClause
End Property

Public Property Let Text(Value As String)
    Me.Caption = " " & Value
End Property

Public Property Let Mode(Value As FormMode)
    mMode = Value
    btnOK.Enabled = (mMode = FormMode.Prompting)
    txtWhereClause.Enabled = btnOK.Enabled
End Property

Private Sub btnOK_Click()
    mDialogResult = vbOK
    ThisWorkbook.Names("WhereClause").Value = txtWhereClause
    gRefreshing = True
    Call HideForm
End Sub

Private Sub btnCancel_Click()
    gRefreshing = False
    Call HideForm
End Sub

Private Sub HideForm()
    mStartUpPosition = 0 'Manual
    mTop = Me.Top
    mLeft = Me.Left
    Me.Hide
End Sub

Private Sub UserForm_Activate()
    mDialogResult = vbCancel
    
    Me.StartUpPosition = mStartUpPosition
    If (mStartUpPosition = 0) Then '0=Manual
        Me.Top = mTop
        Me.Left = mLeft
    End If
    
End Sub

Private Sub UserForm_Initialize()
    mMode = FormMode.Prompting
    mStartUpPosition = Me.StartUpPosition
    mTop = Me.Top
    mLeft = Me.Left
    txtWhereClause = ThisWorkbook.Names("WhereClause").Value
    txtWhereClause = Mid(txtWhereClause, 3, Len(txtWhereClause) - 3)
End Sub

Public gRefreshing As Boolean

Public Sub PromptRefresh()

    With frmPrompt
        .Mode = FormMode.Prompting
        .Text = "Refresh XYZ Report Worksheet"
        .Show vbModal
        If (.DialogResult = vbOK) Then
            .Mode = FormMode.Executing
            .Text = "Refreshing XYZ Report Worksheet ..."
            .Show vbModeless
            DoEvents
            Call ExecuteRefresh(.WhereClause)
        End If
    End With
    Unload frmPrompt
    
End Sub

Private Sub ExecuteRefresh(WhereClause As String)
    Do While (gRefreshing)
        DoEvents
    Loop
End Sub

 

[combo]

You can simplify the code. You can set startup position to CenterOwner in design mode. The instance of such userform will be shown at the centre of the screen on startup. At this moment this setting can be changed to manual. When you hide the form and show again, it will appear at the same place where it was hideden. This will not influe the project settings.
As long as you do not plan more complex behaviour, this will do all that (button triggered):

Private Sub UserForm_Activate()
Me.StartUpPosition = 0
End Sub

Private Sub CommandButton1_Click()
With Me
    .Hide
    .Show vbModeless
End With
End Sub

Private Sub CommandButton2_Click()
With Me
    .Hide
    .Show vbModal
End With
End Sub

combo