Calendar to insert dates in a form

[Steen]

Hi

Im pretty new in VBA but have managed to create the Calender following the
information given in http://www.fontstuff.com/vba/vbatut07.htm. Now i wood
like to use this for inserting dates in a form (Start Date and EndDate). I
have created the form and made all the code and is seems to work. I am using
to txt fields for entering the start og end dates. I woold like the Calendar
to be the only tool used for entrering dates (no manual date written) - how
can I do this task?

/Stony

 

[Ron de Bruin]

Hi Steen

Userform or Sheet ?

See this page
http://www.rondebruin.nl/calendar.htm

 

[Steen]

Useform

Hi Steen

Userform or Sheet ?

See this page
http://www.rondebruin.nl/calendar.htm

 

[Ron de Bruin]

Hi Steen

Try this basic example

Add two userforms to the workbook
One with only the calendar named Userform1

Add this code event

Private Sub Calendar1_Click()
UserForm2.ActiveControl.Value = Me.Calendar1.Value
Unload Me
End Sub

And one with the name Userform2 with this code

Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
UserForm1.Show
End Sub

Private Sub TextBox2_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
UserForm1.Show
End Sub


Run the userform named Userform2 to test

 

[Ron de Bruin]

Add one line in the click event

Private Sub Calendar1_Click()
UserForm2.ActiveControl.Value = Me.Calendar1.Value
UserForm2.ActiveControl.SetFocus
Unload Me
End Sub


Good night

 

[Steen]

Hi Ron

Hope you had a good sleep you deserve it

You are the best - works great.

Two questions:
1. Can the format be changed - it displays dates in the format "mm/dd/yyyy"
and I would like it to be "yyyy.mm.dd"
2. I am using the dates to filter a list of dates and with the format used
by the Calendar (mm/dd/yyyy) everything works , but with the other date
format there seems to be a problem (yyyy.mm.dd). The filter (custom) is setup
right but the filter dosn't work right before I activate i manualy once more
- can you help on that?

-------------------------------------------
If ErrorHandling Then
...
Else
Dim sStart, sEnd As String
sStart = txtStartDate.Value
sEnd = txtEndDate.Value
Selection.AutoFilter Field:=3, Criteria1:=">=" & sStart,
Operator:=xlAnd, Criteria2:="<=" & sEnd
End If
--------------------

 

[Ron de Bruin]

Hi Steen

You can use the format function for the display

UserForm2.ActiveControl.Value = Format(Me.Calendar1.Value, "yyyy.mm.dd")


Use Dateserial if you want to filter on dates with code
I like to use 3 comboboxes if you want to filter on dates (See EasyFilter)
and use the combobox names in the DateSerial

rng.AutoFilter Field:=4, Criteria1:=">=" & DateSerial(1947, 2, 23), _
Operator:=xlOr, Criteria2:="<=" & DateSerial(1988, 5, 7) ' yyyy-mm-dd format