PDA

View Full Version : Solved: Using one Calendar in multiple userform textboxes



av8tordude
07-11-2010, 09:23 AM
I have multiple textboxes in different userforms that are being used to enter dates. I have only one Calendar userform in my workbook. When I enter a specific userform texbox, the Calendar userform appears. How can I enter a date into the selected textbox, but be able to use the same calendar userform in a different userform textbox?

thanks for you help.

p45cal
07-11-2010, 12:59 PM
Don't use textboxes for this, use date and time picker controls instead (additional controls..., Microsoft Date and Time Picker Control).
Attaching a version of you workbook would be useful.

mikerickson
07-11-2010, 01:56 PM
Here is an example.
In the attached, there is a simple calandar userfrom, ufCalandar.
Note that pressing OK hides the form and pressing Cancel unloads the form.
(There is also a method, SetDate, that sets the values of the controls to a specific date. It defaults to today's date.)

The function (in a normal module) DateEnteredOnForm will invoke the userform and return the date set by the user.
Note that if the user cancels, the ufCalandar in the second With block is a different instance of the userform than the one mentioned in the first With block.
The sub TryTheFunction uses DateEnteredOnForm, with the syntax
valueReturned = DateEnteredOnForm()'in a normal module
Function DateEnteredOnForm(Optional DefaultDate As Date) As Date
If DefaultDate = 0 Then DefaultDate = Date

With ufCalandar
.SetDate (DefaultDate)
.Show
End With

With ufCalandar
If .lbxDate.ListIndex = -1 Then
Rem cancel pressed
DateEnteredOnForm = CDate(0)
Else
DateEnteredOnForm = DateSerial(1990 + .lbxYear.ListIndex, .lbxMonth.ListIndex + 1, .lbxDate.ListIndex + 1)
End If
End With

Unload ufCalandar
End Function
That function can be (renamed uiDate and) put in the ufCalandar's code module and called in sub TryFromForm, with the syntax
valueReturned = ufCalandar.uiDate'in ufCalandar's code module.

Function uiDate(Optional DefaultDate As Date) As Date
If DefaultDate = 0 Then DefaultDate = Date

With Me
.SetDate (DefaultDate)
.Show
End With

With ufCalandar
If .lbxDate.ListIndex = -1 Then
Rem cancel pressed
uiDate = CDate(0)
Else
uiDate = DateSerial(1990 + .lbxYear.ListIndex, .lbxMonth.ListIndex + 1, .lbxDate.ListIndex + 1)
End If
End With

Unload ufCalandar
End Function


Then, from any userform, this form can be used to return a date.
TextBox1 of Userform2 shows one way.

in UserForm2's code module.
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
calldatetotextbox (TextBox1)
KeyAscii = 0
End Sub

Private Sub TextBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
Call DateToTextbox(TextBox1)
End Sub

Sub DateToTextbox(aTextBox As MSForms.TextBox)
Dim Default As Date, dateReturned As Date
Default = Date
With aTextBox
If IsDate(.Text) Then
Default = DateValue(.Text)
End If
dateReturned = ufCalandar.uiDate(Default)
If dateReturned = 0 Then
Rem cancel pressed
Else
.Text = Format(dateReturned, "d mmm yyyy")
End If
End With
End Sub

av8tordude
07-28-2010, 01:16 PM
Thank you mikerickson for you help. :friends: