date Calender user form

[sam1]

Hi,

Is it possible to create a calender drop down as a user form, thus when
then date is select via the drop down, it is set as a value in the user
form. Similar to the date calender on webpages ?

 

[weavtennis]

There are two methods for this...

Both involve using the "event" for the control.

1) If using a calendar control (standard Excel/windows calendar), the
"click" event should be sufficient. When the user clicks on the
calendar (date button of a specific month), the value (properly
formatted) can be posted to a label or text box.

2) if using a dropdown list, populate the list with dates and then
using the "change" event, populate the label or the text box.

Examples below:

where the main userform is called "InputChildAge" and contains a label
to hold the date called "Label1"

1) using a separate calendar
Private Sub Calendar1_Click()
InputChildAge.Label1.Caption =
Format(InputChildAge.Calendar1.Value, "MMMM DD, YYYY")
End Sub

2) using a dropdown...
Private Sub ComboBox1_Change()
InputChildAge.Label1.Caption =
Format(InputChildAge.Dropdown1.Value, "MMMM DD, YYYY")
End Sub

 

[Arvi Laanemets]

Hi

A 3rd method (without using any VBA):

On a separate (hidden) sheet, create a list of available dates (it can be
static, or dynamic, where available dates are created depending p.e. on
current date.). Define this date list as named range (again, it can be
defined as static or dynamic range - it depends, is the length of date list
fixed, or changes). Format the list in any valid date format, you think to
be bequem for user to use when selecting the date.

Format as data validation list all cells where you want to select dates. Set
list source to be equal to created named range. Format those cells in any
valid date format (It can be different from format you used in date list).
It's al.

The user can select dates from data validation drop-down lists, or enter
them manually. And unless you declare otherwise when implementing data
validation, the user can enter only values present in date list on hidden
sheet, or clear cell values. (Especially I advice to use this method, when
you need select values in many cells.)

Arvi Laanemets