Page 1 of 2 12 LastLast
Results 1 to 10 of 16

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

  1. #1
    Board Regular
    Join Date
    Apr 2016
    Posts
    60
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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 by SamDsouza; Jun 16th, 2019 at 08:51 AM.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,983
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    43 Thread(s)

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

    How about
    Code:
    With txtDate
       .Value = Worksheets("Sheet1").Range("A2").Value
       If .Value <> "" Then .Value = Format(.Value, "dd-mmm-yyyy")
    End With
    Last edited by Fluff; Jun 16th, 2019 at 08:54 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Apr 2016
    Posts
    60
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,983
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    43 Thread(s)

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

    Replace you code with mine, don't change it.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Apr 2016
    Posts
    60
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    Replace you code with mine, don't change it.
    Changed it. still same error

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,983
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    43 Thread(s)

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

    What is your code now & is txtDate on the userform you are initialising, or userform1?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Apr 2016
    Posts
    60
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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 by SamDsouza; Jun 16th, 2019 at 09:39 AM.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,983
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    43 Thread(s)

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

    That is not the same as the code I posted.
    Try my suggestion WITHOUT changing it.
    Last edited by Fluff; Jun 16th, 2019 at 09:47 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Apr 2016
    Posts
    60
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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 by SamDsouza; Jun 16th, 2019 at 09:54 AM.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,983
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    43 Thread(s)

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

    It replaces the line in red
    Code:
    Private Sub UserForm_Initialize()
    Load UserForm1
    UserForm1.Show vbModeless
    txtDate.Value = Format(CDate(Worksheets("Sheet1").Range("A2").Value), "dd-mmm-yyyy")
    
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •