Decent Output for Date required as Blank cell which is Empty also in TextBox instead of 30-Dec-1899

[SamDsouza]

Hello
I've already formatted column 4 with date format as shown below.
Have also explored that if Date is Blank then the value of that cell of 0 date is 30-Dec-1899. Anyways that i can prevent 30-Dec-1899 which are displayed if the cells are blank and cells to remain blank too if no date is entered

So when userform in initialized txtDate.Text displays as 30-Dec-1899. so how can this txtField can remain Blank when UF is initialized

coding ThisWorkbook

Private Sub Workbook_Open()
   Worksheets("Sheet1").Columns(4).NumberFormat = "dd-mmm-yyyy"
End Sub

userform1

Option Explicit
Dim disableEvents As Boolean
Public dDate As Date

Private Sub txtDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
  Worksheets("Sheet1").Columns(1).NumberFormat = "dd-mmm-yyyy"

If txtDate.Value = vbNullString Then
    Exit Sub
ElseIf Not IsDate(txtDate.Value) Then
    Cancel = True
    MsgBox "Invalid date, please re-enter", vbCritical
    txtDate.Value = vbNullString
    txtDate.SetFocus
     Worksheets("Sheet1").Range("A2").Value = Format(CDate(txtDate.Value), "dd-mmm-yyyy")

    Exit Sub
End If
dDate = DateSerial(Year(Date), Month(Date), Day(Date))
txtDate.Value = Format(CDate(txtDate.Value), "dd-mmm-yyyy")
Worksheets("Sheet1").Range("A2").Value = Format(CDate(txtDate.Value), "dd-mmm-yyyy")
End Sub

Private Sub UserForm_Initialize()
Load UserForm1
UserForm1.Show vbModeless
txtDate.Value = Format(CDate(Worksheets("Sheet1").Range("A2").Value), "dd-mmm-yyyy")

End Sub

SamD

 

[Fluff]

How about

With txtDate
   .Value = Worksheets("Sheet1").Range("A2").Value
   If .Value <> "" Then .Value = Format(.Value, "dd-mmm-yyyy")
End With

 

[SamDsouza]

Fluff tried

Private Sub UserForm_Initialize()
Load UserForm1
UserForm1.Show vbModeless
With txtDate.Value = Format(CDate(Worksheets("Sheet1").Range("A2").Value), "dd-mmm-yyyy")
     If .Value = "" Then .Value = Format(.Value, "dd-mmm-yyyy")
End With
End Sub

Object required Error 424

 

[Fluff]

Replace you code with mine, don't change it.

 

[SamDsouza]

Replace you code with mine, don't change it.

Changed it. still same error

 

[Fluff]

What is your code now & is txtDate on the userform you are initialising, or userform1?

 

[SamDsouza]

Code remains Same as per post #1
txtDate.Text is very much on userform1
As per your suggestion changed it see below

Private Sub UserForm_Initialize()
Load UserForm1
UserForm1.Show vbModeless
With txtDate.Value = Format(Worksheets("Sheet1").Range("A2").Value, "dd-mmm-yyyy")
     If .Value = "" Then .Value = Format(.Value, "dd-mmm-yyyy")
End With
End Sub

 

[Fluff]

That is not the same as the code I posted.
Try my suggestion WITHOUT changing it.

 

[SamDsouza]

How aboutCode:

With txtDate
   .Value = Worksheets("Sheet1").Range("A2").Value
   If .Value <> "" Then .Value = Format(.Value, "dd-mmm-yyyy")
End With

As the above was not clear i did as per your suggestion and implemented in UF intialize. So where was your code supposed to be placed

 

[Fluff]

It replaces the line in red

Private Sub UserForm_Initialize()
Load UserForm1
UserForm1.Show vbModeless
[COLOR=#ff0000]txtDate.Value = Format(CDate(Worksheets("Sheet1").Range("A2").Value), "dd-mmm-yyyy")[/COLOR]

End Sub