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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
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
76,230
Office Version
  1. 365
Platform
  1. Windows
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

ADVERTISEMENT

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
76,230
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

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
Top