set datepicker value to current date on workbook open

[whitwoo]

As the title really I want to be able to set a datepicker DTPicker21 to todays date when the workbook is opened
Secondally I would like to be able to set a second datepicker DTPicker22 to todays date plus 1 year

So basically what do I need to add to the Workbook_Open function?

As I use the change to a datepicker to hide various columns (Code below) I think the next challenge will then be how to also hide those columns when the dates are set on open.


'h

ide columns less than start date'
Private Sub DTPicker21_Change()
    Application.ScreenUpdating = False
    For Each Cll In Range("F3:FF3")
        If Cll.Value < DTPicker21.Value Or Cll.Value > DTPicker22.Value Then Cll.EntireColumn.Hidden = True Else Cll.EntireColumn.Hidden = False
    Next Cll
    Application.ScreenUpdating = True
    Set WBOpen = False
End Sub

'hide columns greaterthan than end date'
Private Sub DTPicker22_Change()
    Application.ScreenUpdating = False
    For Each Cll In Range("F3:FF3")
        If Cll.Value > DTPicker22.Value Or Cll.Value < DTPicker21.Value Then Cll.EntireColumn.Hidden = True Else Cll.EntireColumn.Hidden = False
    Next Cll
    Application.ScreenUpdating = True
    Set WBOpen = False
End Sub

Complete novice thrashing posted examples

Thanks in advance

[Mumps1]

In the code window for the userform, try:

Private Sub UserForm_Initialize()
        DTPicker1.value = Date
End Sub

[whitwoo]

I have seen similar code so I thnk this is along the right lines however (complete novice here) I cant locate the userform code window. I guess this is because I have the datepicker controls on the worksheet and not a form?

[whitwoo]

I have tried including similar in the workbook_open but with no joy

Private Sub Workbook_Open()
    DTPicker21.Value = Date
End Sub

[Mumps1]

I can't seem to find an initialize event if the date picker is on the worksheet. There may be a work-around. Click the 'Developer' tab and then in the 'Controls' group click 'Design Mode'. Right click the date picker and click 'View Code'. Paste this macro into the window:

Private Sub DTPicker21_GotFocus()
    DTPicker21.Value = Date
End Sub

Return to your sheet and click 'Design Mode' to turn it off. When you click on the date picker, it will automatically display today's date. I'm not sure if this works for you. There may be a better solution.

[Mumps1]

Here is another approach. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Activate()
    DTPicker21.Value = Date
End Sub

The date will default to today every time you select the worksheet.

[Mumps1]

Another approach still that meets your original request:

Private Sub Workbook_Open()
    Sheets("Sheet1").DTPicker21.Value = Date
End Sub

Put it in the workbook_open. Change "Sheet1" to suit your needs. Save and close the file. Open the file.

[Mumps1]

It worked for me. After you inserted the macro, did you save the file, close it and then re-open it?