VBA Hide User form but retain data entered into it

Question

I'm back again with what I hope is a fairly easy question.

I'm attempting to create a user form in VBA. The user will enter certain bits of information into the form, and then close the form. I'd like the user form to retain the data entered after it is closed by the user. I'm treating it as a class module, since techinically they are, or at least that is how I understand it. Here is the code I'm using:

In the main sub that displays the user form:

Sub NonACATMemo()

Dim UserInput As MemoReasons
Set UserInput = New MemoReasons
UserInput.Show

... And then in the user form itself to close it...

Private Sub UserForm_Terminate()
MemoReasons.Hide
End Sub

I also call this sub from a command button on the form. The issue I'm running into is that when I use this method, I get an error "Run-time error '402': Must close or hide topmost modal form first." If I use unload me, when I try to get data out of the form it is cleared and I get a "server not available" error or something to that effect.

So, any ideas on hiding a user form but retaining the data inside?

Final couple of notes: This is the only user form in the project, and here is an example of how I'm trying to get data out of it using the Public Property Get method:

Debug.Print UserInput.EmailFlag
Debug.Print UserInput.ContraFirm
Debug.Print UserInput.MemoReason

Well, I'm all ears if anyone has any suggestions.

Solution 1

I've not seen this approach before. Normally, I would just instantiate the form by:

MemoReasons.Show

Indeed the _Terminate() event is wiping out the data held in the form. So the solution is to not call the _Terminate() event from the button-click. Instead, simply hide the form, e.g.:

Sub ShowMemoReasons()
'In a normal code module, this calls the form
' could be run from the macros menu or attached to
' a shape/button/etc on the worksheet.

MemoReasons.Show

End Sub

Put these in the MemoReasons code module:

Private Sub CommandButton1_Click()  '<-- Rename to handle your button's click event

    MemoReasons.Hide  '## Hides the form but does not release it from memory

End Sub
Private Sub UserForm_Terminate()
'Any events pertaining to the termination of the form object
' otherwise, all form control data will be wiped out when
' this object releases from memory

End Sub

After you do these, if you use the button to HIDE the form, you can call the ShowMemoReasons() and it should re-display the form, while preserving data that was previously entered in the form.

If you use the red "X" button or some other event triggers the Terminate event, you will lose the form data. There are ways to do validation and prevent this with the QueryClose event if necessary.

Update

I don't think you need to Dim an instance of the user form (an exception would be if you will be potentially displaying multiple forms at the same time). Otherwise, declaring UserInput as a public variable is redundant and confusing.

Inicidentally, this is why you're getting the error: Must close or hide topmost modal form first. If you must implement it this way, instead of doing MemoReasons.hide you should use Me.Hide.

As long as you are only displaying one instance of the form, you can always refer to MemoReasons.property because MemoReasons is a public object, just like ThisWorkbook or ActiveWorksheet, etc.

Instead, you should be able to refer to this object (MemoReasons is an object) in any subroutine, for example create another one that is not called from the previous subs. Run the sub to show the form, enter in some data, and then hide the form. With the form hidden, then run this subroutine, and you should see the resulting data from the form.

Sub Test2()
    Debug.Print MemoReasons.EmailFlag
End Sub

Solution 2

It's an old topic... hopefully someone will need a help on this.

You can do the following :

1-Place a Close/Cancel button (you can set Cancel property to True)

2-Attach following code to Click event

Private Sub btnClose_Click()
    'Do some stuff if necessary
    Me.Hide
End Sub

3-Attach this code to QueryClose event

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = 0 Then 'vbFormControlMenu = 0 ([X] button on top right), see MSDN
     Cancel = True 'Don't fire Terminate event...
     btnClose_Click '...instead, call my close event handler
   End If
End Sub

You can check the result by placing a debugger breakpoint in UserForm_Initialize event and it should be fired only for the first time showing the UserForm and thus, granting UserForm state preservation.