DTpicker - excel VBA

[pruleone]

Hi

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")
Else
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?

 

[combo]

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

combo

 

[pruleone]

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

 

[combo]

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.

combo