PDA

View Full Version : Trying to display result of function as a userform label control



OCG
12-12-2007, 03:10 PM
Hello,
I am trying to display the result of a custom VBA function as a label on a userform. The function is called and takes arguments from three text boxes on the userform. I attached it to the exit parameter. (This may be part of the problem; I'm not sure).

There seem to be two problems, first all the text boxes don't always contain data, but because the arguments are not optional, I get a type mismatch error when the code runs (I tried switching some of them to optional, but it didn't help as you can see from my 'commented code).
The other problem is that even when all the arguments seem to be met (i.e. the text boxes all have data), I still get a compile error that the argument is not optional. This only happens if I try and pass the result to some aspect of the userform. As you can see from the last coded line in the custom function, if I assign it to a range on the worksheet, it functions fine.

Below are the codes. Any help you can provide would be appreciated. I use excel 03 and windows XP.
Note the exit procedure also contains a code that formats the text box to a specified date format, and this does work.


Private Sub txtStartDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim StartDate As Date
Dim EndDate As Date
If IsDate(Me.txtStartDate.Value) Then
StartDate = Me.txtStartDate.Value
Me.txtStartDate.Value = Format(StartDate, "m/d/yyyy")
Else: MsgBox "Please enter a date"
End If
Call CalcDate(txtStartDate, txtYears, txtMonths, txtDays)
EndDate = CalcDate
lblProjectPeriod.Caption = EndDate
End Sub
and the custom function:

Function CalcDate(StartDate As Date, Optional Years As Variant, Optional Months As Variant, _
Optional Days As Variant) As Date

'If IsMissing(Years) Then Years = False
' If Years = False Then Years = 0
' End If
'End If
'If IsMissing(Months) Then Months = False
' If Months = False Then Years = 0
' End If
' End If
' If IsMissing(Days) Then Days = False
' If Days = False Then Years = 0
' End If
'End If

CalcDate = DateAdd("yyyy", Years, StartDate)
CalcDate = DateAdd("m", Months, CalcDate)
CalcDate = DateAdd("d", Days, CalcDate)
CalcDate = DateAdd("d", -1, CalcDate)

'Range("L7") = CalcDate

Thanks for your help

Bob Phillips
12-12-2007, 05:16 PM
Untested




Private Sub txtStartDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim StartDate As Date
Dim EndDate As Date
If IsDate(Me.txtStartDate.Value) Then
StartDate = Me.txtStartDate.Value
Me.txtStartDate.Value = Format(StartDate, "m/d/yyyy")
Else: MsgBox "Please enter a date"
End If
If txtStartDate.Text <> "" And _
txtYears.Text <> "" And txtMonths.Text <> "" And txtDays.Text <> "" Then
EndDate = CalcDate(CDate(txtStartDate.Text), txtYears.Text, txtMonths.Text, txtDays.Text)
End If
lblProjectPeriod.Caption = Format(EndDate, "m/d/yyyy")
End Sub

OCG
12-13-2007, 10:11 AM
Thanks XLD,

I appreciate the help