DTPicker format

I have a DTPicker in my user form, and I have set the custom format in the properties to display as MM/'dd'/yyyy, but when I submit the information to my document it still displays as m/d/yyyy if the month and day are single digit. I have been modifying the data manually, but is there a way to change the data without having to manually alter it?

Here is my submit code:
Private Sub CommandButton1_Click()
    Dim LastRow As Long, ws As Worksheet, wb As Workbook
    Set wb = ThisWorkbook
    Workbooks("Working.xlsm").Activate
    Set ws = Sheets("FUN1")

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row

    ws.Range("A" & LastRow).Value = TextBox1.Text 'Adds the TextBox1 into Col A & Last Blank Row
    ws.Range("B" & LastRow).Value = Date1.Value 'Adds the Date1 into Col B & Last Blank Row
    ws.Range("C" & LastRow).Value = TextBox5.Text 'Adds the Date1 into Col B & Last Blank Row

End Sub

Open in new window

david franciscoAsked:
Who is Participating?
 
david franciscoAuthor Commented:
It still is displaying the date as m/d/yyyy, but that's ok, I will just switch the DTPicker to a text box and change the data before it even gets submitted. Thanks for the assist though.
0
 
Neil FlemingConsultant and developerCommented:
I am guessing the format you've set applies to the datepicker. The worksheet knows nothing of said format, unfortunately.

But you can easily format the cell as you add the data:
ws.Range("B" & LastRow).Value
ws.Range("B" & LastRow).NumberFormat="Mm/dd/yyyy"

Open in new window

0
 
david franciscoAuthor Commented:
When I added to code to make
Private Sub CommandButton1_Click()
    Dim LastRow As Long, ws As Worksheet, wb As Workbook
    Set wb = ThisWorkbook
    Workbooks("Working.xlsm").Activate
    Set ws = Sheets("FUN1")


    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row

    ws.Range("A" & LastRow).Value = TextBox1.Text 'Adds the TextBox1 into Col A & Last Blank Row
    ws.Range("B" & LastRow).Value = Date1.Value 'Adds the Date1 into Col B & Last Blank Row
    ws.Range("C" & LastRow).Value = TextBox5.Text 'Adds the Date1 into Col B & Last Blank Row
    ws.Range("B" & LastRow).Value
    ws.Range("B" & LastRow).NumberFormat = "Mm/dd/yyyy"
End Sub

Open in new window


I get an error for invalid use of property on
ws.Range("B" & LastRow).Value

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Neil FlemingConsultant and developerCommented:
oh, sorry.. it's just because I copied only part of your code . It should read, as per your original

 ws.Range("B" & LastRow).Value = Date1.Value 'Adds the Date1 into Col B & Last Blank Row
ws.Range("B" & LastRow).NumberFormat = "Mm/dd/yyyy"

Open in new window

0
 
Neil FlemingConsultant and developerCommented:
ah maybe the datepicker is not storing the date as a "real" date anyway?

You could try
 ws.Range("B" & LastRow).Value = cDate(Date1.Value) 'Adds the Date1 into Col B & Last Blank Row
ws.Range("B" & LastRow).NumberFormat = "Mm/dd/yyyy"

Open in new window


Experimentally, you could try formatting the cell to some more elaborate date format, eg:

 ws.Range("B" & LastRow).Value = Date1.Value 'Adds the Date1 into Col B & Last Blank Row
ws.Range("B" & LastRow).NumberFormat = "Mmm/Ddd dd/yyyy"

Open in new window


If that changes nothing then you are probably adding a text version of the date to the cell.
0
 
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorCommented:
I think try this:

1: ws.Range("B" & LastRow).Value = Date1.Value 'Adds the Date1 into Col B & Last Blank Row
2:ws.Range("B" & LastRow).NumberFormat = "yyyy\/mm\/dd"

Regards

Chris
0
 
david franciscoAuthor Commented:
There was no real solution to my issue so I moved on with another option to get the same result.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.