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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
How are you displaying the DatePicker?

Perhaps something like this for the default.
Code:
DTPicker1.Value = Date()
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,214,524
Messages
6,120,049
Members
448,940
Latest member
mdusw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top