Preventing A Userform From Closing

[Ark68]

Please consider this code,

Private Sub create1_Click()
     
    Dim strFilename As String
    Dim ws_vh As Worksheet
    Dim no_rw As Long
    Dim ui1 As VbMsgBoxResult
    Dim oFS As Object
    Dim uimsg As Long
    Dim fw As Long
    
    Application.ScreenUpdating = False
    strFilename = "H:\PWS\Parks\Parks Operations\Sports\Sports15\CLASS_DUMP\schedule.csv"
    Set oFS = CreateObject("Scripting.FileSystemObject")
    
    Set ws_vh = Workbooks("Sports17.xlsm").Worksheets("VAR_HOLD")
    
    f_name = "schedule.csv"
    If CheckFileIsOpen(f_name) = True Then
        Debug.Print "schedule.csv is open and hidden"
        Workbooks(f_name).Close False
    End If
    Debug.Print "schedule.csv is not open. It will be opened now."
    Workbooks.Open Filename:=strFilename                                                            'open schedule.csv
    Workbooks("schedule.csv").Windows(1).Visible = False                                            'hide it
    
    With Workbooks("schedule.csv").Worksheets("schedule")
        no_rw = WorksheetFunction.CountIf(.Range("M:M"), ws_vh.Range("B17"))                         'determine number of rows of raw data that match the selected dates
        ws_vh.Range("F17") = no_rw
    End With
    Application.ScreenUpdating = True
    
    'If rows of data are found for the selected date
    
    If no_rw > 0 Then
        msg_raw_data_in_file.Show
    Else
        msg_data_not_in_schedule.Show
    End If
End Sub

In our example, lets assume no_rw = 21. If no_rw > 0 then open the userform called 'msg_raw_data_in_file'.
The userform opens, and has a command button called "CANCEL". If the user selects this option, he is returned to a default state of the application, which consists of activating the home worksheet and opening the preliminary application's userform (uf1_create-wo1)

Here is the cancel commandbutton code"

Private Sub CommandButton3_Click() 'CANCEL
    Workbooks("schedule.csv").Close savechanges:=False
    Application.ScreenUpdating = True
    Unload msg_raw_data_in_file
    Unload uf1_create_wo1
    uf1_create_wo1.Show
    Workbooks("sports17.xlsm").Worksheets("FRONT").Activate
End Sub

Here is my problem ...
When the above code is executed, the home worksheet is activated, and the userform initiatizes and becomes visible. But after that, the code returns to procedure 'create1_click' at the line highlighted in blue. The line in red unfortunately closes uf1_create_wo1, which I don't want.

What can I do to prevent the userform from closing?

 

[mikerickson]

What are the other options for leaving msg_raw_data_in_file?

The issue is that with a modal serfdom. the Userform1.Show line doesn't finish executing until the user form is either unloaded or hidden.
So msg_raw_data_in_file is in a limbo state, Unloaded, but still waiting for uf1_create_wo1.Show to finish executing.

You could do this by Loading the new form and having the calling routine Show it.

Private Sub CommandButton3_Click() 'CANCEL
    Workbooks("schedule.csv").Close savechanges:=False
    ThisWorkbook.Workbooks("sports17.xlsm").Worksheets("FRONT").Activate
    Application.ScreenUpdating = True
    Unload msg_raw_data_in_file
    Unload uf1_create_wo1
    [COLOR="#FF0000"]Load uf1_create_wo1[/COLOR]
End Sub

Private Sub create1_Click()
    Dim strFilename As String
    Dim ws_vh As Worksheet
    Dim no_rw As Long
    Dim ui1 As VbMsgBoxResult
    Dim oFS As Object
    Dim uimsg As Long
    Dim fw As Long
    
    [COLOR="#FF0000"]Dim otherForm as Object[/COLOR]

    ' ...
    
    If no_rw > 0 Then
        msg_raw_data_in_file.Show
        [COLOR="#FF0000"]For Each otherForm in UserForms
            If otherForm.Name = "uf1_create_wo1" Then
                otherForm.Show
                Exit For
            End If
        Next otherForm[/COLOR]
    Else
        msg_data_not_in_schedule.Show
    End If
End Sub

If create1 is a command button on a user form, it looks like you are piling userform on top of userform on top of userform .... which is a pretty unstable situation.

You might consider using fewer userforms and replacing them with a single userform with a MultiPage control. Rather that closing one form and opening another which closes and opens yet another, just change to different pages on the Multipage.

 

[Ark68]

Hello Mike, thank you for your reply!

I have two other options besides cancel in my userform, Accept ... which (LOL) will close all forms (2 open at this point) and open 1. Exit will exit the application.
I will try your recommendation and advise on it's success. I am learning I don't like working much with userforms.

 

[mikerickson]

Userforms are fun. Multiple user forms (particularly modal) are a pain.

 

[Ark68]

It appears I was unsuccessful with your kind suggestion Mike.

 

[mikerickson]

Then I would recommend the MultiPage approach.

Try to think of each page on the MultiPage as the equivalent of one of your existing user forms.

 

[Ark68]

I will give it a try and see if I can adapt it . Userform 'msg_raw_data_in_file' is basically a glorified message box. It could just as easily be replaced by an interactive message box, but without the customized effects. It really only presented users with options on what to do with userform uf1_create_wo1.

Thanks again!

 

[mikerickson]

If the purpose of msg_raw_data_in_file is to get user input about how uf1_create_wo1 should act, then it should be a part of uf1_create_wo1.

From the names, it sounds like msg_data_not_in_schedule is very similar to msg_raw_data_in_file, i.e. a glorified MsgBox.
If that's the case, then combining the two and passing Label captions and/or hide/show controls depending on the situation, would make life easier.

 

[Ark68]

Yeah ... that is clearly an angle I missed! I'll get to work!

 

[Ark68]

For the most part, I have managed to adapt my code to rely on only one form, uf1_create_wo1. But I have one instance where I'm unable to get away with doing this.

from userform uf1_create_wo1:

Private Sub F6C_Accept_Click() 'accept button
    Frame6_c.Visible = False
    Frame6_a.Visible = True
    msg_crp_reqd.Show
End Sub

msg_crp_req'd is a warning message with an option for the user to choose whether they require step by step instructions (which will open another series of graphic userforms while uf1_create_wo1 remains open ... a potential problem) or not. After the selection is made, the user selecting OK closes msg_crp_reqd closes with the intention of uf1_create_wo1 being available. It closes.

Here is the OK button from msg_crp_redq:

Private Sub CommandButton5_Click() 'OK
    Unload Me
End Sub