DTPicker value NULL

[benjamint]

Hi,

I'm using DatePicker (DTPicker) in my userform, and I want the user to fill in a start-date and end-date, but when I add the DatePicker, it automatically sets the date to today. I want it to be clear, and to let the user fill it in. How can I do that?

 

[Domenic]

How about using the Initalize event to set the default date to the current date when the UserForm is displayed? If so, try...

Private Sub UserForm_Initialize()
    Me.DTPicker1.Value = Date
End Sub

Note that the above code needs to be placed in the UserForm code module (right-click the UserForm, and select View Code).

 

[Rick Rothstein]

Put the following code in the UserForm's code module and the DatePicker control will be blank when the UserForm first displays...

Private Sub DTPicker1_CloseUp()
  FormatDTPicker
End Sub

Private Sub DTPicker1_Format(ByVal CallbackField As String, FormattedString As String)
  If CallbackField = "X" Then FormattedString = ""
End Sub

Private Sub DTPicker1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As _
                                stdole.OLE_XPOS_PIXELS, ByVal y As stdole.OLE_YPOS_PIXELS)
  With DTPicker1
    If .Value = vbNull Then
      .Value = Now
    End If
  End With
End Sub

Private Sub UserForm_Initialize()
  DTPicker1.Value = vbNull
  FormatDTPicker
End Sub

Private Sub FormatDTPicker()
  With DTPicker1
    If .Value = vbNull Then
      .Format = dtpCustom
      .CustomFormat = "X"
    Else
      .Format = dtpShortDate
    End If
  End With
End Sub

Note that the above code uses the UserForm's Initialize event and the DatePicker's CloseUp, Format and MouseDown events, so if you have existing code in these events, you will have to combine your code and my code for them so that each event procedure only appears once. If you want more direct control of the blanking-out process (say, by using a CommandButton), just take the code in the UserForm's Initialize event and put it in the appropriate code procedure (such as the Click event for a CommandButton).

 

[benjamint]

Hi Rick,

Thanks for your support. Your code works, but when I unload the userform, I see that it automatically took the date of today with it's time. Is it possible to get an emtpy field after unloading?

Beside that, I just saw, when I click on a date, it still remains empty. Is it possible to let it be visible after filling in the date?

 

[Rick Rothstein]

Thanks for your support. Your code works, but when I unload the userform, I see that it automatically took the date of today with it's time. Is it possible to get an emtpy field after unloading?

No, the control always has a date in its field because there is no such thing as a "null date" in VB... the code I gave you simply hides that date from the user. If you need to test whether the user selected a date or not, the control's value property will contain the date 12/31/1899 if the "nothing" is displayed in the control.

Beside that, I just saw, when I click on a date, it still remains empty. Is it possible to let it be visible after filling in the date?

When I click on a date from the drop-down calendar, that date shows in the control. Are you saying it does not do that for you? What version of Excel are you using? I just tested my code in XL2003 and XL2007 and the selected date shows in the control for both of those versions.

 

[benjamint]

No, the control always has a date in its field because there is no such thing as a "null date" in <ACRONYM title=vBulletin>VB</ACRONYM>... the code I gave you simply hides that date from the user. If you need to test whether the user selected a date or not, the control's value property will contain the date 12/31/1899 if the "nothing" is displayed in the control.

Is there maybe a possibility to force the user to fill in a date? So if the date is empty, and the user clicks on submit, he should get a messagebox with "Please fill in the start-date"

When I click on a date from the drop-down calendar, that date shows in the control. Are you saying it does not do that for you? What version of Excel are you using? I just tested my code in XL2003 and XL2007 and the selected date shows in the control for both of those versions.

Sorry, my bad, it works perfectly. By the way, I'm using XL2010.

 

[Rick Rothstein]

Is there maybe a possibility to force the user to fill in a date? So if the date is empty, and the user clicks on submit, he should get a messagebox with "Please fill in the start-date"

Add this code to the UserForm's code module...

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If DTPicker1.Value = DateSerial(1899, 12, 31) Then
    MsgBox "Please fill in the start-date"
    Cancel = True
  End If
End Sub

When the user tries to close the UserForm, the above code will check to see if the DatePicker control has a date or not.

 

[benjamint]

Wow, you're amazing!!!! Thank you so much.

One last question, and after this I won't bother you anymore:
When I enter a date, it shows me also the time.
What I see now is: 30-10-2012 10:36
What I want to see is: 30-10-2012

 

[Rick Rothstein]

Wow, you're amazing!!!! Thank you so much.

One last question, and after this I won't bother you anymore:
When I enter a date, it shows me also the time.
What I see now is: 30-10-2012 10:36
What I want to see is: 30-10-2012

You caught me just before I am going to sleep for the night. You don't see the time in the DatePicker control right? You see it in the returned value from the control. You can remove the time part by wrapping the returned value with the DateValue function...

MsgBox DateValue(DTPicker1.Value)

 

[benjamint]

Great!!! Once again thank you!!

Have a good night/sleep!!