theta
03-20-2013, 11:10 AM
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
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