Userform same Calendar used with 2 Userform textboxes

[LNG2013]

I have 2 textboxes DataBox1 and DataBox2, I have them both configured that when they are clicked a calendar pops up for the user to select the date. However, I can't figure out how to make it the Calendar fill the slected TextBox, right now I have it working for DataBox1 but can't figure out how to make it work for 1 & 2.

Option Explicit
'Thanks to fontstuff for the example code
' ===================================================
' Code by Martin Green    eMail [EMAIL="martin@fontstuff.com"]martin@fontstuff.com[/EMAIL]
' Visit my Office Tips web site at [URL="http://www.fontstuff.com"]www.fontstuff.com[/URL]
' ===================================================
Private Sub cmdClose_Click()
' Close the UserForm
    Unload Me
End Sub
Private Sub UserForm_Initialize()
' Check if active cell contains a date. If 'yes' show
' same date on calendar. If 'no' show today's date.
    If IsDate(DataReport.DateBox1.Value) Then
        Calendar1.Value = DateValue(DataReport.DateBox1.Value)
    Else
        Calendar1.Value = Date
    End If
End Sub
Private Sub Calendar1_Click()
' Transfer date selected on calendar to active cell
' and close UserForm.
    DataReport.DateBox1.Value = Calendar1.Value
    Unload Me
End Sub

 

[Trevor G]

Which version of Excel are you using?

I have just tried this and it works OK.

Created userform with Calendar Control.
Add 2 textboxes named them txtStart and txtEnd
Added a command button named it cmdDates.

Double click the cmdDates button and added the following code.

Private Sub cmdDates_Click()
If Me.cmdDates.Caption = "Start Date" Then
Me.txtStart = Me.Calendar1.Value
Me.cmdDates.Caption = "End Date"
ElseIf Me.cmdDates.Caption = "End Date" Then
Me.txtEnd = Me.Calendar1.Value
Me.cmdDates.Caption = "Start Date"
End If

End Sub

 

[Andrew Poulsom]

What code are you using to show the UserForm? If you Hide the UserForm instead of Unloading it, the calling procedures will have access to the Calendar's current value.

 

[LNG2013]

I am using Excel 2003,

Here is the rest of the code for the DataReport that is the form the TextBoxes are on.
Trevor - is there a way to do this without an additional button. I just wanted the Users to click the textboxt, calendar pops up, they select their date in each one then run the report.

Private Sub UserForm_Initialize()
 
        Me.GoalBox1.Enabled = False
        Me.ObjBox1.Enabled = False
        Me.RunButton.Enabled = False
 
    With DateBox1
        .Text = "mm/dd/yyyy"
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
        With DateBox2
        .Text = "mm/dd/yyyy"
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
End Sub
Private Sub DateBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
DateBox1.Text = ""
    frmCalendar.Show
End Sub
Private Sub DateBox2_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
DateBox2.Text = ""
    frmCalendar.Show
End Sub
Private Sub ComboBox1_Change()
    If Objective5m.FilterMode = True Then: Objective5m.ShowAllData
    Objective5m.Range("A1").AutoFilter Field:=14, Criteria1:="=" & DataReport.ComboBox1.Value
 
    ' ~~~ This fills the GoalBox with the possible Goals
        DataReport.GoalBox1.Clear
    Call FillCombobox(Objective5m.Range("P2", Objective5m.Cells(Rows.Count, "P").End(xlUp)).SpecialCells(xlCellTypeVisible), DataReport.GoalBox1)
 
    Me.GoalBox1.Enabled = True
 
End Sub
 
Private Sub GoalBox1_Change()
    DataReport.ObjBox1.Clear
    Objective5m.Range("A1").AutoFilter Field:=16, Criteria1:="=" & DataReport.GoalBox1.Value
    Call FillCombobox(Objective5m.Range("Q2", Objective5m.Cells(Rows.Count, "Q").End(xlUp)).SpecialCells(xlCellTypeVisible), DataReport.ObjBox1)
 
    Me.ObjBox1.Enabled = True
    Me.RunButton.Enabled = True
End Sub
Private Sub ObjBox1_Change()
    Objective5m.Range("A1").AutoFilter Field:=17, Criteria1:="=" & DataReport.ObjBox1.Value
End Sub
Private Sub RunButton_Click()
Unload DataReport
'
' This code adds the ability to select the month range for the autofilter
' Field:=24 refers to the 24th column
 
Rows("1").AutoFilter Field:=9, _
                     Criteria1:=">=" & Me.DateBox1, _
                     Operator:=xlAnd, _
                     Criteria2:="<=" & Me.DateBox2
Call Filtered
Call CheckForNoData
End Sub
Private Sub CancelButton_Click()
Unload DataReport
End Sub

 

[Andrew Poulsom]

Example:

'UserForm DataReport
 
Private Sub DateBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    frmCalendar.Show
    DateBox1.Value = frmCalendar.Calendar1.Value
    Unload frmCalendar
End Sub
 
Private Sub DateBox2_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    frmCalendar.Show
    DateBox2.Value = frmCalendar.Calendar1.Value
    Unload frmCalendar
End Sub
 
'UserForm frmCalendar
 
Private Sub Calendar1_Click()
    Me.Hide
End Sub

 

[LNG2013]

That worked like a charm Andrew thank you very much. Iwas wracking my brain on how to do this, and began thinking I was going to have to assign each one of the DateBoxes as a variable.