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

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
137
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
Code:
Private Sub Workbook_Open()
   Worksheets("Sheet1").Columns(4).NumberFormat = "dd-mmm-yyyy"
End Sub
userform1
Code:
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
 
Last edited:

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,067
Office Version
365
Platform
Windows
How about
Code:
With txtDate
   .Value = Worksheets("Sheet1").Range("A2").Value
   If .Value <> "" Then .Value = Format(.Value, "dd-mmm-yyyy")
End With
 
Last edited:

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
137
Fluff tried
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,067
Office Version
365
Platform
Windows
Replace you code with mine, don't change it.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,067
Office Version
365
Platform
Windows
What is your code now & is txtDate on the userform you are initialising, or userform1?
 

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
137
Code remains Same as per post #1
txtDate.Text is very much on userform1
As per your suggestion changed it see below
Code:
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,067
Office Version
365
Platform
Windows
That is not the same as the code I posted.
Try my suggestion WITHOUT changing it.
 
Last edited:

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
137
How aboutCode:
Code:
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,067
Office Version
365
Platform
Windows
It replaces the line in red
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,753
Messages
5,470,571
Members
406,707
Latest member
drkjz

This Week's Hot Topics

Top