The Date Picker drop-down calendar maintains the date the worksheet was last saved.  I want the drop-down calendar to default to today's date when opening the worksheet.  What's the simplest solution?  I'm running Windows 7 and Office 2013.  Thanks to anyone who is able to help me out.

You could try something like

Private Sub Workbook_Open()
Dim bSaved As Boolean
Dim objDP As OLEObject

    On Error GoTo errExit
    bSaved = ThisWorkbook.Saved
    Set objDP = ThisWorkbook.Worksheets("Sheet1").OLEObjects("DTPicker21")
    objDP.Object.Value = Date
    ThisWorkbook.Saved = bSaved
    
errExit:
    If objDP Is Nothing Then
       ' MsgBox "DTPicker21 not found"
    End If
End Sub

Change the Sheet name and object name to suit.

I don't follow how a date picker "maintains" the date the file was saved, especially not if you change it in the open event. Maybe write the date and time to a cell when saved in the BeforeSave event eg
Thisworkbook.Worksheets(1).Range("A1") = Now

You could try something like

Private Sub Workbook_Open()
Dim bSaved As Boolean
Dim objDP As OLEObject

    On Error GoTo errExit
    bSaved = ThisWorkbook.Saved
    Set objDP = ThisWorkbook.Worksheets("Sheet1").OLEObjects("DTPicker21")
    objDP.Object.Value = Date
    ThisWorkbook.Saved = bSaved
    
errExit:
    If objDP Is Nothing Then
       ' MsgBox "DTPicker21 not found"
    End If
End Sub

Change the Sheet name and object name to suit.

I don't follow how a date picker "maintains" the date the file was saved, especially not if you change it in the open event. Maybe write the date and time to a cell when saved in the BeforeSave event eg
Thisworkbook.Worksheets(1).Range("A1") = Now

• Marked as answer by Melon Chen CHNMicrosoft contingent staff, Moderator 22 hours 19 minutes ago

You could try something like

Private Sub Workbook_Open()
Dim bSaved As Boolean
Dim objDP As OLEObject

    On Error GoTo errExit
    bSaved = ThisWorkbook.Saved
    Set objDP = ThisWorkbook.Worksheets("Sheet1").OLEObjects("DTPicker21")
    objDP.Object.Value = Date
    ThisWorkbook.Saved = bSaved
    
errExit:
    If objDP Is Nothing Then
       ' MsgBox "DTPicker21 not found"
    End If
End Sub

Change the Sheet name and object name to suit.

I don't follow how a date picker "maintains" the date the file was saved, especially not if you change it in the open event. Maybe write the date and time to a cell when saved in the BeforeSave event eg
Thisworkbook.Worksheets(1).Range("A1") = Now

• Marked as answer by Melon Chen CHNMicrosoft contingent staff, Moderator Thursday, February 19, 2015 1:33 PM

You could try something like

Private Sub Workbook_Open()
Dim bSaved As Boolean
Dim objDP As OLEObject

    On Error GoTo errExit
    bSaved = ThisWorkbook.Saved
    Set objDP = ThisWorkbook.Worksheets("Sheet1").OLEObjects("DTPicker21")
    objDP.Object.Value = Date
    ThisWorkbook.Saved = bSaved
    
errExit:
    If objDP Is Nothing Then
       ' MsgBox "DTPicker21 not found"
    End If
End Sub

Change the Sheet name and object name to suit.

I don't follow how a date picker "maintains" the date the file was saved, especially not if you change it in the open event. Maybe write the date and time to a cell when saved in the BeforeSave event eg
Thisworkbook.Worksheets(1).Range("A1") = Now

• Marked as answer by Melon Chen CHNMicrosoft contingent staff, Moderator Thursday, February 19, 2015 1:33 PM

Peter, thanks for the response.  I copied your VBA code and included it in one of my spreadsheets, and now the DatePicker drop-down calendar automatically comes up when the file is opened to today's date.  Wow, you don't know how long I've been trying to get around this issue.  I do not quite understand all the code you have written, but it worked, and that's what counts.  I really appreciate your help.  I'm going to reopen the file tomorrow just to make sure the calendar comes up with tomorrow's date before adding this code to other spreadsheets. 

I did have to save the file as a macro-enabled file in order for your code to work.

Peter,

Some of my worksheets have multiple DTPickers, named such as DTPicker1, DTPicker2, & DTPicker3.

How does this affect your line of code:

Set objDP = ThisWorkbook.Worksheets("Sheet1").OLEObjects("DTPicker21")

Can you enter multiple names where you have the green "Sheet1" and "DTPicker21"?  I noticed you used on that line of code the plural of "Worksheet" & "OLEObject". 

Thank you for your assistance, I really appreciate it.

Darrell

WorksheetS and oleObjectS refer to collections of the respective objects, hence plural.

You need to write separate code for each calendar so you need to know the name of each object. Objects are named with their original name followed by a sequential number when the object was added. The Date Picker name is "DTPicker2" followed by the 1 if the first DP. You can get all OLEObject names with

Sub test()
Dim ole As OLEObject
    For Each ole In ActiveSheet.OLEObjects
        Debug.Print ole.Name
    Next
End Sub

 

Peter, thanks once again. 

(1) When you say that the Date Picker name is "DTPicker2", followed by a sequence number, then does that mean that no matter what name I enter under "Properties", for purposes of the VBA code, it will recognize it as DTPicker21, or DTPicker22, or DTPicker23, etc.?

(2) Is the DTPicker2 name related to what shows up in the formula bar when I click on the object itself:

=EMBED("MSComCtl2.DTPicker.2","")

(3) Does it matter whether you call it DTPicker2 or DTPicker.2 (with a period)?

(4) Is it essential that I save the worksheet as "macro-enabled" when inserting the VBA code you have given me?  If that's the case, adding VBA code in effect is similar to adding a macro?

I hope you don't mind my sending you these additional questions!  Boy am I glad I found you!!

Thanks again,

Darrell

When controls are inserted they are given default names, typically similar to the classname. followed by an incremented counter. The classname is always the same, in this case "DTPicker.2". The "2" distinguishes from the earlier version of the control "DTPicker.1".

The name given to identify the control for code purposes can be changed to anything you want, eg "myDTPalpha". You can do this manually, while in Design mode select the control and rename it in the Name box, or with code after inserting it. Therafter in your code refer to is as activesheet.OLEObjects("myDTPalpha"), instead of (say) "DTPicker21"

Re your 3). The full class name is only needed for creating the control and yes it must include the dot "DTPicker.2" Later refer to the control by its default name (without a dot) or whatever name you gave it.

4)  Yes any workbook with code must be saved as xlsm or xlsb and the user's security must allow macros

• Edited by Peter Thornton (Excel MVP 2008-13) 23 hours 18 minutes ago

When controls are inserted they are given default names, typically similar to the classname. followed by an incremented counter. The classname is always the same, in this case "DTPicker.2". The "2" distinguishes from the earlier version of the control "DTPicker.1".

The name given to identify the control for code purposes can be changed to anything you want, eg "myDTPalpha". You can do this manually, while in Design mode select the control and rename it in the Name box, or with code after inserting it. Therafter in your code refer to is as activesheet.OLEObjects("myDTPalpha"), instead of (say) "DTPicker21"

Re your 3). The full class name is only needed for creating the control and yes it must include the dot "DTPicker.2" Later refer to the control by its default name (without a dot) or whatever name you gave it.

4)  Yes any workbook with code must be saved as xlsm or xlsb and the user's security must allow macros

• Edited by Peter Thornton (Excel MVP 2008-13) Saturday, February 21, 2015 12:35 PM

Hi Peter,

Since your last reply to my Date Picker issue, I have tried your instructions with varying degrees of success.  I still am having trouble when a form has multiple DTPickers.  Here's my latest attempt at one of the forms:  I am confused as to where the sub test () goes.  Also, in the private sub workbook (open), you can see my humble attempts to name each sheet and datepicker with its own code as you suggested.  Can you please tell me what I'm doing wrong?  I'd really appreciate it.  Darrell

Sub test()
Dim ole As OLEObject
    For Each ole In ActiveSheet.OLEObjects
        Debug.Print ole.Name
    Next
End Sub

Private Sub Workbook_Open()
Dim bSaved As Boolean
Dim objDP As OLEObject

    On Error GoTo errExit
    bSaved = ThisWorkbook.Saved
    Set objDP = ThisWorkbook.Worksheets("Trip Data Input").OLEObjects("DTPicker1")
    objDP.Object.Value = Date
    Set objDP = ThisWorkbook.Worksheets("Trip Data Input").OLEObjects("DTPicker2")
    objDP.Object.Value = Date
    Set objDP = ThisWorkbook.Worksheets("Requisition").OLEObjects("DTPicker1")
    objDP.Object.Value = Date
   
    ThisWorkbook.Saved = bSaved
   
errExit:
    If objDP Is Nothing Then
       ' MsgBox "DTPicker1 not found"
    End If
End Sub

The VBA code above was entered on "this workbook" under VBA project, using the drop downs "workbook" and "open"