Cannot test us userform unloaded / terminated

[theta]

Hi all

I have a single userform in one workbook that is used for date inputs on several other workbooks (all with a reference to the master workbook).

To get around this I had to use this approach (which took a long time to find)

Call userform as a Public Function :

Public Function DatePicker(Optional ByRef objReturn, Optional blnReal As Boolean = True, Optional blnClosed As Boolean = False)

Dim UF As Object
Dim strRealDate As String 'Real date
Dim dblDecimalDate As Double 'Decimal date
Dim genDate 'No type assigned

'Set the userform as an object that can be references from another workbook
Set UF = Get_frmDatePicker()

'Set objReturn if missing, define blnReal if missing
If IsMissing(objReturn) Then Set objReturn = ActiveCell

'Show the userform for input, control passed to userform until hidden / unloaded
UF.Show

'Form was unloaded
If UF Is Nothing Then Exit Function

'-----------------------------------------------
strRealDate = UF.lbRealDate
dblDecimalDate = DateToDecimal(CDate(strRealDate))

If blnReal Then genDate = strRealDate Else genDate = dblDecimalDate

'Complete appropriate action for object type
Select Case TypeName(objReturn)
Case "Range"
objReturn.Value = "=" & DateToFraction(genDate) 'IEEE fraction
Case "TextBox"
objReturn.Value = genDate 'limited DP accuracy
Case "Label"
objReturn.Caption = genDate 'limited DP accuracy
End Select

Unload UF

Set UF = Nothing

End Function


This is the Get_frmDatePicker call, set as a frmDatePicker (the form name) and sets the function as this object - allowing access from other workbooks outside the main workbook and the returning of variables between userforms.


Public Function Get_frmDatePicker() As frmDatePicker

Set Get_frmDatePicker = frmDatePicker

End Function


The problem I have is that if the user closes the frmDatePicker, I cannot test if UF is Nothing. It returns false. But trying to access UF.lbRealDate will return an automation error as it was already terminated.

Any ideas please? Just need to test if unloaded

[theta]

Although thinking on this more. The object is still set (as it is a local object within a public function) but it not available as it has been closed.

How would one get around this?

[Aflatoon]

The user should not be able to unload the form - only your code should be able to do that. The user should only ever be able to hide it and the calling code then unloads it.

[SamT]

IMO, that's a messed up way of doing what you want. Bear with us and we will get you going right.

You have a Workbook "Master.xlsx" which set dates in one or more of ManyWorkbooks( "WkBk1", WkBk2", "WkBK3. etc)

The DateSetting action is triggered by activity in one or more of ManyWorkBooks.

Right so far?

Questions:

Is the date set in all of ManyWorkBooks.
Is it set in more than one sheet or range in any of the ManyWorkBooks?
What is/are the nature(s) of the activities that trigger DateSetting?

[mikerickson]

Try something like this (assume your uf is named Userform1)


Public Function DatePicker(Optional ByRef objReturn, Optional blnReal As Boolean = True, Optional blnClosed As Boolean =
me.Tag = "run"
'more code

If Userform1.Tag = "run" Then
MsgBox "User did not close Userform"
Else
MsgBox "User closed userform"
End If

Unload Userform1
End Function


The "Userform1" in the If line will either refer to Me (if the user hides the form) or it will create (but not show) a new instance of Userform1.

In the first case, Userform1.Tag will be "run" (as set in the first line of the function)
In the second case, the Userform1.Tag will be the default, vbNullString.

Thus giving you the ability to detect if the user hid out of the form (presumably by pressing an OK key) or closed the userform (Cancel or corner click.)

Note that in the IF section, the keyword Me cannot be used since Me might be in the process of unloading. The name of the userform "Userform1" needs to be used.

[theta]

I have not seen the .Tag property used before, I will research it.

The main reason I did it this way is because I need to share values between user forms e.g.

Workbook_X, frmBirthdayList has a command button to the frmDatePicker

Workbook_Y, frmDatePicker is initialised for selection, then OK pressed

Workbook_X, frmBirthdayList receives value via the function

[Aflatoon]

FYI you can create a form that can be declared and loaded from other workbooks (just as you can with 'normal' class modules) as long as they have a reference to the project that contains the form.

[theta]

FYI you can create a form that can be declared and loaded from other workbooks (just as you can with 'normal' class modules) as long as they have a reference to the project that contains the form.

How might I do this? Could you please provide a simple example. I settled on this method as all others failed - passing variables between two userforms from two different workbooks.

:dunno

[Aflatoon]

If you export the form from the master workbook and then edit the .frm file so it has:
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True

and reimport it then you can declare and instantiate the form from another workbook. Assuming the master workbook has a project named prjMaster, in the calling workbook you can use
Dim frm As prjMaster.Form_name
Set frm = New prjMaster.Form_name
frm.Caption = "called from " & thisworkbook.name
frm.Show
and thus use its attributes as though it were a local form.


Disclaimer: I have never had a need to do this in real life so could not in all honesty say if there are any issues with it.

[Jan Karel Pieterse]

I would simply use the QueryClose event of the userform like so:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode <> 1 Then
Cancel = True
Me.Hide
End If
End Sub