Default ActiveX Date Picker to todays

[I heart Excel]

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

 

[Ruddles]

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

Put this somewhere in the Workbook_Open code module:-

[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.

 

[I heart Excel]

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?!

 

[Ruddles]

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.

 

[I heart Excel]

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

 

[Ruddles]

What sort of form are you using if it's not a userform?

 

[I heart Excel]

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,

 

[RoryA]

The basic code would be something like:

   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.

 

[I heart Excel]

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

 

[RoryA]

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'"