DTPicker - VBA Code to Set Default Value

Woohoo

New Member
Joined
Mar 11, 2005
Messages
8
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,275
How are you displaying the DatePicker?

Perhaps something like this for the default.
Code:
DTPicker1.Value = Date()
 

Woohoo

New Member
Joined
Mar 11, 2005
Messages
8
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,275
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.

Code:
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

New Member
Joined
Mar 11, 2005
Messages
8
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,275
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

New Member
Joined
Mar 11, 2005
Messages
8
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.
 

Forum statistics

Threads
1,077,931
Messages
5,337,264
Members
399,137
Latest member
Westland01

Some videos you may like

This Week's Hot Topics

Top