Default ActiveX Date Picker to todays

I heart Excel

Board Regular
Joined
Feb 28, 2011
Messages
66
Hello, Again,

Sorry to ask another question, spent all day yesterday trying to figure stuff out, but am really stuck on two things.

One is defaulting the ActiveX date picker to todays date when the document is open.

The second if whether you are able to sum the difference between ActiveX Time pickers?

Thanks very much
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
One is defaulting the ActiveX date picker to todays date when the document is open.

Put this somewhere in the Workbook_Open code module:-
Code:
[I]nameofdatepicker [/I]= Date

The second if whether you are able to sum the difference between ActiveX Time pickers?

Dates are just numbers: if you want to work out the difference, just subtract one from the other.
 
Last edited:
Upvote 0
I entered -

Private Sub Workbook_Open()
DTPicker1 = Date
End Sub

I close the form and then open it and it isn't working. What am I doing wrong?!
 
Upvote 0
You put it in the event which handles the workbook opening. (You said "when the document is open" so I assumed you meant workbook.)

Put it in the Userform_Initialize or Userform_Activate event.
 
Upvote 0
I'm not using a UserForm.

I have tried a number of things now.

I have found one that is -


Private Sub Workbook_Open()Sheets("Sheet name").DTPicker1.Value = DateEnd Sub</PRE>
But I want it for all the Date pickers on the Form, and I don't want the Date Pickers containing the time to default.

I pressumed this would be quite simple, but am having real issues figuring this out!!

I want to open the form and all the possible Dates be defaulted to todays date.

Thanks
 
Upvote 0
What sort of form are you using if it's not a userform?
 
Upvote 0
Just a normal Excel spreadsheet and the VBA functions, sorry I'm relatively new to Excel, I did have a look at userforms and as I have a deadline wasn't sure if I would have time to look into this just yet, but it is something I want to learn.

Thanks,
 
Upvote 0
The basic code would be something like:
Code:
   Dim objOLE As Object
   For Each objOLE In Sheets("Sheet2").OLEObjects
      If TypeName(objOLE.Object) = "DTPicker" Then
         objOLE.Object.Value = Date + 1
      End If
   Next objOLE

but I really wouldn't recommend using lots of (or indeed any) ActiveX controls on a worksheet. They are not that stable generally.
 
Upvote 0
I put in the above code and amended the Sheet name -

Dim objOLE As Object
For Each objOLE In Sheets("Annual Leave Form").OLEObjects
If TypeName(objOLE.Object) = "DTPicker" Then
objOLE.Object.Value = Date + 1
End If
Next objOLE

And I keep getting the following error on line -
For Each objOLE In Sheets("Annual Leave Form").OLEObjects

The error reads -
Complie error - invalid outside procedure

I am putting the form on 'thisworkbook'


I had another look at userforms after what you said above, and have decided this will need a good long morning to read through the processes.

Thanks for you help
 
Upvote 0
That code needs to go into the Workbook_Open event code - not by itself.
I have absolutely no clue what you mean by "I am putting the form on 'thisworkbook'"
 
Upvote 0

Forum statistics

Threads
1,224,542
Messages
6,179,421
Members
452,913
Latest member
JWD210

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