Date formating when saving to excel sheet

Jan 7th 2015

#1

i Am trying to save the date in this why DD/MM/YY not MM/DD/YY i have tryed Cdate but i cannot get it working when i have blank cells i only fill in textboxes that need filling and sometime i have to delete dates when the person no longer have the goods see code

Code

Private Sub save_Click()
Dim iRow As Long
Dim ws As Worksheet
Sheets("Data").Unprotect password:="pejabe"
Set ws = Worksheets("Data")
iRow = Range(Me.TextBox7).Row
  'check for a name
If Trim(Me.TextBox1.Value) = "" Then
  Me.TextBox1.SetFocus
  MsgBox "Please enter a first name"
  Exit Sub
  Else: Me.TextBox1.Value = Me.TextBox1.Value
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox1.Value
ws.Cells(iRow, 2).Value = Me.TextBox2.Value
ws.Cells(iRow, 3).Value = Me.Num1.Value
ws.Cells(iRow, 4).Value = Me.TextBox8.Text
ws.Cells(iRow, 5).Value = Me.Num2.Value
ws.Cells(iRow, 6).Value = (Me.TextBox9.Text)
ws.Cells(iRow, 7).Value = Me.Num3.Value
ws.Cells(iRow, 8).Value = (Me.TextBox10.Text)
ws.Cells(iRow, 9).Value = Me.Num4.Value
ws.Cells(iRow, 10).Value = (Me.TextBox11.Text)
ws.Cells(iRow, 11).Value = Me.Num5.Value
ws.Cells(iRow, 12).Value = (Me.TextBox12.Text)
ws.Cells(iRow, 13).Value = Me.Num6.Value
ws.Cells(iRow, 14).Value = (Me.TextBox13.Text)
ws.Cells(iRow, 15).Value = Me.Num7.Value
ws.Cells(iRow, 16).Value = (Me.TextBox14.Text)
ws.Cells(iRow, 17).Value = Me.Num8.Value
ws.Cells(iRow, 18).Value = (Me.TextBox15.Text)
ws.Cells(iRow, 19).Value = Me.Num9.Value
ws.Cells(iRow, 20).Value = (Me.TextBox16.Text)
ws.Cells(iRow, 21).Value = Me.Num10.Value
ws.Cells(iRow, 22).Value = (Me.TextBox17.Value)
ws.Cells(iRow, 23).Value = Me.Num11.Value
ws.Cells(iRow, 24).Value = (Me.TextBox18.Value)
ws.Cells(iRow, 25).Value = Me.Num12.Value
ws.Cells(iRow, 26).Value = (Me.TextBox19.Value)
ws.Cells(iRow, 27).Value = Me.Num13.Value
ws.Cells(iRow, 28).Value = (Me.TextBox20.Value)
ws.Cells(iRow, 29).Value = Me.Num14.Value
ws.Cells(iRow, 30).Value = (Me.TextBox21.Value)
ws.Cells(iRow, 31).Value = Me.Num15.Value
ws.Cells(iRow, 32).Value = (Me.TextBox22.Value)
ws.Cells(iRow, 33).Value = Me.Num16.Value
ws.Cells(iRow, 34).Value = (Me.TextBox23.Value)
ws.Cells(iRow, 35).Value = Me.Num17.Value
ws.Cells(iRow, 36).Value = (Me.TextBox24.Value)
ws.Cells(iRow, 37).Value = Me.Num18.Value
ws.Cells(iRow, 38).Value = (Me.TextBox25.Value)
    
'ws.Cells(iRow, 11).Value = Me.TextBox8.Value
 
 Sheets("Data").Protect password:="pejabe"
 Me.hide
 Unload Me
    UserForm3.Show
End Sub

Display More

Luke M

Moderator

Reactions Received

5

Points

7,520

Posts

1,493

Operating System

Windows (x64)

Excel Version

Office 365
Jan 7th 2015
- #2
Re: Date formating when saving to excel sheet

Have you tried changing the format of the cell you are transferring the data to? As your UserForm has generic names for all it's parts, we have no idea which lines are talking about dates.
Best Regards,
Luke M
=======
"A little knowledge is a dangerous thing."
pejabe

Beginner

Points

115

Posts

15
Jan 7th 2015
- #3
Re: Date formating when saving to excel sheet

Yes I have tried that where it says me.textbox that's all my date boxes
ashu1990

Intermediate

Points

2,040

Posts

384
Jan 7th 2015
- #4
Re: Date formating when saving to excel sheet

you should use

Code

format(Me.TextBox1.Value,"DD/MM/YYYY")

but remember while retriving dates you should make the format as mm/dd/yyyy or else it will make the DD as MM by default....
pejabe

Beginner

Points

115

Posts

15
Jan 7th 2015
- #5
Re: Date formating when saving to excel sheet

Ok Thanks I will try tomorrow.

Jan 8th 2015

#6

Re: Date formating when saving to excel sheet

sorry i cant get it working its showing it right on the userform but not in excel sheet i have formatted the excel sheet to uk DD/MM/YY
this is what i have done to the code it really doing my head in i did try and upload file but did not work

Code

ws.Cells(iRow, 1).Value = Me.TextBox1.Value
ws.Cells(iRow, 2).Value = Me.TextBox2.Value
ws.Cells(iRow, 3).Value = Me.Num1.Value
ws.Cells(iRow, 4).Value = Format(Me.TextBox8.Value, "DD/MM/YY")
ws.Cells(iRow, 5).Value = Me.Num2.Value
ws.Cells(iRow, 6).Value = Format(Me.TextBox9.Value, "DD/MM/YY")
ws.Cells(iRow, 7).Value = Me.Num3.Value
ws.Cells(iRow, 8).Value = Format(Me.TextBox10.Value, "DD/MM/YY")
ws.Cells(iRow, 9).Value = Me.Num4.Value
ws.Cells(iRow, 10).Value = Format(Me.TextBox11.Value, "DD/MM/YY")
ws.Cells(iRow, 11).Value = Me.Num5.Value
ws.Cells(iRow, 12).Value = Format(Me.TextBox12.Value, "DD/MM/YY")
ws.Cells(iRow, 13).Value = Me.Num6.Value
ws.Cells(iRow, 14).Value = Format(Me.TextBox13.Value, "DD/MM/YY")
ws.Cells(iRow, 15).Value = Me.Num7.Value
ws.Cells(iRow, 16).Value = Format(Me.TextBox14.Value, "DD/MM/YY")
ws.Cells(iRow, 17).Value = Me.Num8.Value
ws.Cells(iRow, 18).Value = Format(Me.TextBox15.Value, "DD/MM/YY")
ws.Cells(iRow, 19).Value = Me.Num9.Value
ws.Cells(iRow, 20).Value = Format(Me.TextBox16.Value, "DD/MM/YY")
ws.Cells(iRow, 21).Value = Me.Num10.Value
ws.Cells(iRow, 22).Value = Format(Me.TextBox17.Value, "DD/MM/YY")
ws.Cells(iRow, 23).Value = Me.Num11.Value
ws.Cells(iRow, 24).Value = Format(Me.TextBox18.Value, "DD/MM/YY")
ws.Cells(iRow, 25).Value = Me.Num12.Value
ws.Cells(iRow, 26).Value = Format(Me.TextBox19.Value, "DD/MM/YY")
ws.Cells(iRow, 27).Value = Me.Num13.Value
ws.Cells(iRow, 28).Value = Format(Me.TextBox20.Value, "DD/MM/YY")
ws.Cells(iRow, 29).Value = Me.Num14.Value
ws.Cells(iRow, 30).Value = Format(Me.TextBox21.Value, "DD/MM/YY")
ws.Cells(iRow, 31).Value = Me.Num15.Value
ws.Cells(iRow, 32).Value = Format(Me.TextBox22.Value, "DD/MM/YY")
ws.Cells(iRow, 33).Value = Me.Num16.Value
ws.Cells(iRow, 34).Value = Format(Me.TextBox23.Value, "DD/MM/YY")
ws.Cells(iRow, 35).Value = Me.Num17.Value
ws.Cells(iRow, 36).Value = Format(Me.TextBox24.Value, "DD/MM/YY")
ws.Cells(iRow, 37).Value = Me.Num18.Value
ws.Cells(iRow, 38).Value = Format(Me.TextBox25.Value, "DD/MM/YY")

Display More

ashu1990

Intermediate

Points

2,040

Posts

384
Jan 8th 2015
- #7
Re: Date formating when saving to excel sheet

pejabe you are making the date formated into the code so no need of formating the excel range to Uk DD/MM/YY this is making the data again into MM/DD/YY...
pejabe

Beginner

Points

115

Posts

15
Jan 8th 2015
- #8
Re: Date formating when saving to excel sheet

the excel sheet is formatted to General the userform shows it correct but when viewing excel sheet it shows in US i not sure whats going on i have delete and started again still no look
pejabe

Beginner

Points

115

Posts

15
Jan 8th 2015
- #9
Re: Date formating when saving to excel sheet

i managed to download my file in this forum here http://www.excelforum.com/exce…rmatting-in-userform.html
norie

Super Moderator

Reactions Received

9

Points

53,549

Posts

10,650
Jan 8th 2015
- #10
Re: Date formating when saving to excel sheet

Don't use Format if you want real dates, use CDate or DateValue but check there's a date in the textbox before you do anything.

Code

If Me.TextBox8.Value <> "" And IsDate(Me.TextBox8.Value Then ws.Cells(iRow, 4).Value = Format(Me.TextBox8.Value, "DD/MM/YY") End If

PS You should consider using a listbox.
Boo!:yikes:
pejabe

Beginner

Points

115

Posts

15
Jan 9th 2015
- #11
Re: Date formating when saving to excel sheet

i would like to thank everyone that helped i have found the solution all i needed to do was change "DD/MM/YY") to "DD/MMM/YYYY") and it works perfect

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login

Share

This site uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close

Your browser has JavaScript disabled. If you would like to use all features of this site, it is mandatory to enable JavaScript.