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
205
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:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
How about
Code:
With txtDate
   .Value = Worksheets("Sheet1").Range("A2").Value
   If .Value <> "" Then .Value = Format(.Value, "dd-mmm-yyyy")
End With
 
Last edited:
Upvote 0
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
 
Upvote 0
Replace you code with mine, don't change it.
 
Upvote 0
What is your code now & is txtDate on the userform you are initialising, or userform1?
 
Upvote 0
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:
Upvote 0
That is not the same as the code I posted.
Try my suggestion WITHOUT changing it.
 
Last edited:
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top