set datepicker value to current date on workbook open

**whitwoo** · 12-17-2014, 10:35 AM

As the title really I want to be able to set a datepicker DTPicker21 to todays date when the workbook is opened
Secondally I would like to be able to set a second datepicker DTPicker22 to todays date plus 1 year

So basically what do I need to add to the Workbook_Open function?

As I use the change to a datepicker to hide various columns (Code below) I think the next challenge will then be how to also hide those columns when the dates are set on open.

'h

Please Login or Register  to view this content.

Complete novice thrashing posted examples

Thanks in advance

**Mumps1** · 12-17-2014, 10:51 AM

In the code window for the userform, try:

Please Login or Register  to view this content.

**whitwoo** · 12-17-2014, 11:08 AM

I have seen similar code so I thnk this is along the right lines however (complete novice here) I cant locate the userform code window. I guess this is because I have the datepicker controls on the worksheet and not a form?

**whitwoo** · 12-17-2014, 11:11 AM

I have tried including similar in the workbook_open but with no joy

Please Login or Register  to view this content.

**Mumps1** · 12-17-2014, 11:23 AM

I can't seem to find an initialize event if the date picker is on the worksheet. There may be a work-around. Click the 'Developer' tab and then in the 'Controls' group click 'Design Mode'. Right click the date picker and click 'View Code'. Paste this macro into the window:

Please Login or Register  to view this content.

Return to your sheet and click 'Design Mode' to turn it off. When you click on the date picker, it will automatically display today's date. I'm not sure if this works for you. There may be a better solution.

**Mumps1** · 12-17-2014, 11:32 AM

Here is another approach. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Please Login or Register  to view this content.

The date will default to today every time you select the worksheet.

**Mumps1** · 12-17-2014, 11:38 AM

Another approach still that meets your original request:

Please Login or Register  to view this content.

Put it in the workbook_open. Change "Sheet1" to suit your needs. Save and close the file. Open the file.

**whitwoo** · 12-17-2014, 01:24 PM

Resource Trial.xlsm

Thanks for your responses Mumps1. It feels like the last option should work but it isnt

I have attached the file to see if this helps make any sense.

Cheers

**Mumps1** · 12-17-2014, 01:52 PM

It worked for me. After you inserted the macro, did you save the file, close it and then re-open it?

set datepicker value to current date on workbook open

LinkBack

Thread Tools

Rate This Thread

Display

set datepicker value to current date on workbook open

Re: set datepicker value to current date on workbook open

Re: set datepicker value to current date on workbook open

Re: set datepicker value to current date on workbook open

Re: set datepicker value to current date on workbook open

Re: set datepicker value to current date on workbook open

Re: set datepicker value to current date on workbook open

Re: set datepicker value to current date on workbook open

Re: set datepicker value to current date on workbook open

Thread Information

Users Browsing this Thread

Similar Threads

Open a workbook off my desktop then closing current workbook

Excel Log: VBA to Add Row(s) Upon Workbook Open Last through Current Date

Open workbook, copy rows, paste to end of current workbook

[SOLVED] VBA to Open File (filename contains current date) & copy Sheet to active workbook

How to open,save and close another workbook from the current workbook using macro?

Bookmarks

Bookmarks

Posting Permissions