Date picker control

[Guest]

I have added two date picker controls to a worksheet.

On one control:
I would like to default to today's date whenever the workbook is opened.

On the other control:
I would like to default the date to today + 2 weeks.

How do i set a default value for these controls?


-- Thanks in advance

 

[Guest]

Open VBA Editor ans select This Workbook in the project browser
Something similar to this in a workbook open event.

Private Sub Workbook_Open() 'Must be this sub name
Dim TodaysDate As Date
Dim TodayFortnightDate As Date
Sheets("ExcelControls").Select 'Use your sheet name
TodaysDate = Date
TodayFortnightDate = Date + 14
Sheets("ExcelControls").CommandButton1.Caption = Format _
(TodaysDate, "dd/mm/yyyy")
Sheets("ExcelControls").CommandButton2.Caption = Format _
(TodayFortnightDate , "dd/mm/yyyy")
End Sub

 

[Guest]

I have had another read of your request and now I am not sure whether it is
the default value to process that you want to change or the caption. If it is
the value then basically the same but substitue caption with value and you
may be able to leave the format function out. However, you cannot set a value
for a control button and I don't know what sort of control you have so my
example is for a ComboBox
eg. Sheets("ExcelControls").ComboBox1.Value = TodaysDate

 

[Guest]

Thanks for the reply.

I am using the DTPicker control to set and select dates.
However i have now worked out how to set the default date in this type of
control.

For your information i have included my method:

Sub SetDefaultDates()

' GeraldM 28/02/07
' sets default from and to dates

dteFromDate = Now() - 2 'default from-date to now minus n days
dteToDate = Now() + 14 'default from-date to now plus n days

Sheet3.dtpFromDate.Day = Day(dteFromDate)
Sheet3.dtpFromDate.Month = Month(dteFromDate)
Sheet3.dtpFromDate.Year = Year(dteFromDate)

Sheet3.dtpToDate.Day = Day(dteToDate)
Sheet3.dtpToDate.Month = Month(dteToDate)
Sheet3.dtpToDate.Year = Year(dteToDate)

End Sub