CODEWITHSUNDEEP
vba
Igor Cheglakov
Igor Cheglakov

Reputation: 555

How to check if a userform is closed with "X" Windows button?

There is a sub, it creates a CourtForm userform and then takes a data from it. The problem appears when said form is closed prematurely, by pressing "X" window button and I get a runtime error somewhere later. For reference, this is what I'm talking about:

enter image description here In my code I tried to make a check to exit sub:

Private Sub test()

    'Create an exemplar of a form
    Dim CourtForm As New FormSelectCourt
    CourtForm.Show

    'The form is terminated at this point

    'Checking if the form is terminated. The check always fails. Form exists but without any data.
    If CourtForm Is Nothing Then
        Exit Sub
    End If

    'This code executes when the form proceeds as usual, recieves 
    '.CourtName and .CourtType variable data and then .hide itself.

    CourtName = CourtForm.CourtName
    CourtType = CourtForm.CourtType
    Unload CourtForm


    'Rest of the code, with the form closed a runtime error occurs here

End Sub

Apparently the exemplar of the form exists, but without any data. Here's a screenshot of the watch:

enter image description here

How do I make a proper check for the form if it's closed prematurely?

Upvotes: 4

Views: 4013

Answers (3)

Walter Rauschenberger
Walter Rauschenberger

Reputation: 1

Likely the most simple way:

  1. In the component any procedure shows the UserForm declare:
Public Terminated As Boolean
  1. In the UserForm write:
Private Sub UserForm_Terminate()
    <component>.Terminated = True
End Sub
  1. In the calling procedure write:
    <userform>.Show
    If Terminated Then ....

Upvotes: 0

Storax
Storax

Reputation: 12177

Add the following code to your userform

    Private m_Cancelled As Boolean

    ' Returns the cancelled value to the calling procedure
    Public Property Get Cancelled() As Boolean
        Cancelled = m_Cancelled
    End Property

    Private Sub UserForm_QueryClose(Cancel As Integer _
                                           , CloseMode As Integer)

        ' Prevent the form being unloaded
        If CloseMode = vbFormControlMenu Then Cancel = True

        ' Hide the Userform and set cancelled to true
        Hide
        m_Cancelled = True

    End Sub

Code taken from here. I would really recommend to have a read there as you will find a pretty good basic explanation how to use a userform.

Upvotes: 4

omegastripes
omegastripes

Reputation: 12612

One of the possible solutions is to pass a dictionary to the user form, and store all entered data into it. Here is the example:

User form module code:

' Add reference to Microsoft Scripting Runtime
' Assumed the userform with 2 listbox and button

Option Explicit

Public data As New Dictionary

Private Sub UserForm_Initialize()

    Me.ListBox1.List = Array("item1", "item2", "item3")
    Me.ListBox2.List = Array("item1", "item2", "item3")

End Sub

Private Sub CommandButton1_Click()

    data("quit") = False
    data("courtName") = Me.ListBox1.Value
    data("courtType") = Me.ListBox2.Value
    Unload Me

End Sub

Standard module code:

Option Explicit

Sub test()

    Dim data As New Dictionary

    data("quit") = True
    Load UserForm1
    Set UserForm1.data = data
    UserForm1.Show

    If data("quit") Then
        MsgBox "Ввод данных отменен пользователем"
        Exit Sub
    End If
    MsgBox data("courtName")
    MsgBox data("courtType")

End Sub

Note the user form in that case can be closed (i. e. unloaded) right after all data is filled in and action button is clicked by user.

Another way is to check if the user form actually loaded:

Sub test()

    UserForm1.Show

    If Not isUserFormLoaded("UserForm1") Then
        MsgBox "Ввод данных отменен пользователем"
        Exit Sub
    End If

End Sub

Function isUserFormLoaded(userFormName As String) As Boolean

    Dim uf As Object

    For Each uf In UserForms
        If LCase(uf.Name) = LCase(userFormName) Then
            isUserFormLoaded = True
            Exit Function
        End If
    Next

End Function

Upvotes: 1

Related Questions