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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.