DTPicker value NULL

benjamint · Oct 29, 2012

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?

silentwolf · Dec 6, 2015

Hi Rick,
hope it is ok to write here as I do have a question regarding your code.
As I am looking for the same kind of code I did used yours..
However I get a runtime error 3557 'a date you specified is not within min or max ...
do you know what the problem could be ? Do I need to change at the properties of the dtpicker dates? And if to what values.

Thanks for your help!

Kind regards

Albert

samzion · Aug 19, 2019

Hi Rick, I tried your code but i am getting an error which says : ""VBA Compile Error: Ambiguous name detected". Please help me on this error

[FONT=&quot][/FONT]Private Sub UserForm_Initialize()
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
End Sub

Rick Rothstein · Aug 19, 2019

samzion said:
Hi Rick, I tried your code but i am getting an error which says : ""VBA Compile Error: Ambiguous name detected". Please help me on this error

Private Sub UserForm_Initialize()
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
End Sub

It is hard to tell what you are attempting to do given that your code is not shown indented, but to answer your direct question... you have two "Private Sub UserForm_Initialize()" in the same module... the VBA compiler cannot process two procedures with the same name. Since Initialize is an event procedure, you cannot simply rename one of them, rather, you will have to put the code from one of them into the other and delete the one you did that from. I see another problem in that you have two procedure headers one after the other...

Private Sub UserForm_Initialize()
Private Sub DTPicker1_CloseUp()

and then you have two End Subs at the end... the VBA compiler will choke on that once you fix the one it complained about first.

samzion · Aug 19, 2019

Rick, thanks for your spontaneous reply. I just copy pasted your code to get rid of default date and i got this error. Could you please correct this error for me bcoz i am just a beginner and i don't know programming.

DTPicker value NULL

benjamint

Board Regular

silentwolf

Well-known Member

Excel Facts

samzion

New Member

Rick Rothstein

MrExcel MVP

samzion

New Member

Similar threads

Forum statistics

Share this page

DTPicker value NULL

benjamint

Board Regular

silentwolf

Well-known Member

Excel Facts

samzion

New Member

Rick Rothstein

MrExcel MVP

samzion

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock