DTPicker - VBA Code to Set Default Value

[Woohoo]

I have a DTPicker control on a spreadsheet and I want to be able to change it's default value (to today's date) when the sheet opens up. I'd also like to change the Min and Max values based on today's date. Any ideas on code for this one?

DTPicker - VBA Code to Set Default Value

 

[Norie]

How are you displaying the DatePicker?

Perhaps something like this for the default.

DTPicker1.Value = Date()

 

[Woohoo]

Thanks for your suggestion. I'm displaying it directly in the sheet, not on a form. I'm not sure how to include the code to be honest. Do I include it in the code for the DTPicker or somewhere else? I'm a bit lost!

 

[Norie]

Sorry I don't quite get what you mean.

Do you mean you went to More Controls on the Control Toolbox toolbar?

If you did that I wouls suggest putting the code into the Activate event for the worksheet the control is on.

Private Sub Worksheet_Activate()
    DTPicker1.Value = Date
    
    DTPicker1.MaxDate = DateSerial(Year(Date), 12, 31)
    DTPicker1.MinDate = DateSerial(Year(Date), 1, 1)
    
End Sub

To access the worksheet module right click the worksheet tab and select View Code.

 

[Woohoo]

Thanks for your suggestion. I'm displaying it directly in the sheet, not on a form. I'm not sure how to include the code to be honest. Do I include it in the code for the DTPicker or somewhere else? I'm a bit lost!

 

[Norie]

Did you read my post? I thought it explained clearly how to use the code.

You put it in the worksheet module, which as I said can be accessed by right clicking the worksheet tab and selecting View Code.

 

[Woohoo]

For some reason my reply got posted twice so I expect this might too.

Thanks for that, it works perfectly. The problem was I didn't know where to put the line of code. I tried putting it into the workbook code in the Workbook_Open routine but that did not work.

 

[Greg Truby]

You put it in the worksheet module, which can be accessed by right clicking the worksheet tab and selecting View Code.