Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

DTpicker - excel VBA

DTpicker - excel VBA

DTpicker - excel VBA


Building some UserForms into excel and using also DTPicker component.
As by default I saw today's date in field then googled and found way to set date field empty and "force" user to select proper date.
Code I used:


With Me.DTPicker1
 .CustomFormat = " "
 .Format = dtpCustom
 End With]With Me.DTPicker1
 .CustomFormat = " "
 .Format = dtpCustom
 End With 

Start was good but then I wanted to update this logic in the way that if in dedicated excel sheet there is already date then I should see that date in userform.
So update code slightly (have in excel additional, so called control cell, which calculates is there something in other cell where date is or not):


If Range("L33") > 0 Then
DTPicker1.Value = Range("A33")
With Me.DTPicker1
 .CustomFormat = " "
 .Format = dtpCustom
 End With
End If] 

The last code is called up when UserForm is activated.

Now interesting things started to happen :)

When I open UserForm (no data added) then form is clean and empty. I can write text into text boxes, I can select values/options from drop-downs.
I can also open date picker but what is interesting -> After I select date and go to next field then date will not be visible.
If I close user-form and ask to save in dedicated fields in excel sheet values then date what I selected will appear.
When I open next time user-form I see previously added date. I can change the date and then automatically date what I'm selecting from calendar is nicely visible in UserForm (this is how it should work).

Had an idea that maybe when there is no date in excel sheet (Range("L33") = 0) then this is the reason why don't see a change in user-form.
So tried to do so that after I selected date, then with DTPicker1_Change() function date is put into proper cell in excel sheet and also same code is re-used which in UserForm opening case did set date value empty.
Somehow this doesn't help.

Maybe someone has good idea what to try out?

RE: DTpicker - excel VBA

Format <> Value. You see the date in the format set (" ").


RE: DTpicker - excel VBA

If I understood you correctly than you are referring also to the fact that date format is set to "" and thereby I don't see it UserForm?

I don't understand why this format is not over ruled / calculated when I'm updating /changing date?
I tested with DTPicker_Change() action but somehow it doesn't call up DT formatting logic again when UserForm is Activated.

Just came up that maybe should move DT formatting out from actions happening when UserForm is Activated.
Will test, maybe then I will be able to start influencing DT format when I'm making change in calendar (picking some date).

RE: DTpicker - excel VBA

You set the format of control, not of displayed value. The same refers to formatted cell in excel - any value is displayed according to format set.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close