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

 

[SamDsouza]

repeating the same as per post #7 but with RED

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

 

[Fluff]

You keep converting the first two lines of code, into one line. It needs to be

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

 

[SamDsouza]

Yes, Got it Thanks for clarifying.

ok now when i enter wrong date ie 30-2-19 in txtDate.text it displays as 19-Feb-1930 instead of showing error msg

 

[Fluff]

That's a completely different question, so needs a new thread.

 

[SamDsouza]

That's a completely different question, so needs a new thread.

Everything remains the same coding. for posting new thread i will have to repeat the same. if you still insist then will post a new thread

 

[Fluff]

You originally asked for code that would not enter a bogus if a cell was blank.
You are now talking about entering a date into the textbox.