Date format in Userform

Visy123

New Member
Joined
Jul 13, 2018
Messages
11
Hi All
Just trying to teach myself a little VBA. I have created a user form and when the date is entered in dd/mm/yyyy format it unloads to the database as mm/dd/yyyy I have searched around and tried differing code but to no use any help would be much appreciated I have pasted my attempt below (txt.date) ...no laughing
Code:
Private Sub cmdadd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")


'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


'check for a part number
If Trim(Me.txtclient.Value) = "" Then
  Me.txtclient.SetFocus
  MsgBox "Please enter client name"
  Exit Sub
End If


'copy the data to the database
'use protect and unprotect lines,
'     with your password
'     if worksheet is protected
With ws
'  .Unprotect Password:="password"
  .Cells(iRow, 1).Value = Me.txtclient.Value
  .Cells(iRow, 2).Value = Me.txtname.Value
  .Cells(iRow, 3).Value = Me.txtphone.Value
  .Cells(iRow, 4).Value = Me.txtad.Value
  .Cells(iRow, 5).Value = Me.txtadcolumn.Value
  .Cells(iRow, 6).Value = Me.txtcost.Value
  .Cells(iRow, 7).Value = Me.txtdate.Value
  .Cells(iRow, 8).Value = Me.txtrep.Value
  .Cells(iRow, 9).Value = Me.txtsection.Value
  .Cells(iRow, 10).Value = Me.txtspec.Value
  
'  .Protect Password:="password"
End With


'clear the data
Me.txtclient.Value = ""
Me.txtname.Value = ""
Me.txtphone.Value = ""
Me.txtad.Value = ""
Me.txtadcolumn.Value = ""
Me.txtcost.Value = ""
Me.txtdate.Value = ""
Me.txtrep.Value = ""
Me.txtsection.Value = ""
Me.txtspec.Value = ""
End Sub


Private Sub cmdClose_Click()
  Unload Me
End Sub
 
Last edited by a moderator:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Try,

Code:
Dim date1 As Date

date1 = Format([COLOR=#574123]Me.txtdate.text[/COLOR], "dd-mm-yy")

Code:
[COLOR=#574123].Cells(iRow, 7).Value = date1[/COLOR]
 
Upvote 0
Try,

Code:
Dim date1 As Date

date1 = Format([COLOR=#574123]Me.txtdate.text[/COLOR], "dd-mm-yy")

Code:
[COLOR=#574123].Cells(iRow, 7).Value = date1[/COLOR]


Thank you so much, just a dumb question though ...does this string go into a new module?
 
Upvote 0
Valid question I should've elaborated :)

Code:
[COLOR=#333333][FONT=Verdana]Private Sub cmdadd_Click()[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Dim iRow As Long[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Dim ws As Worksheet
[/FONT][/COLOR][COLOR=#ff0000][FONT=Verdana]Dim date1 As Date

date1 = format(Me.txtdate.text, "dd/mm/yy")
[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Set ws = Worksheets("Data")[/FONT][/COLOR]


[COLOR=#333333][FONT=Verdana]'find first empty row in database[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1[/FONT][/COLOR]


[COLOR=#333333][FONT=Verdana]'check for a part number[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]If Trim(Me.txtclient.Value) = "" Then[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Me.txtclient.SetFocus[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]MsgBox "Please enter client name"[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Exit Sub[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]End If[/FONT][/COLOR]


[COLOR=#333333][FONT=Verdana]'copy the data to the database[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]'use protect and unprotect lines,[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]' with your password[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]' if worksheet is protected[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]With ws[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]' .Unprotect Password:="password"[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Cells(iRow, 1).Value = Me.txtclient.Value[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Cells(iRow, 2).Value = Me.txtname.Value[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Cells(iRow, 3).Value = Me.txtphone.Value[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Cells(iRow, 4).Value = Me.txtad.Value[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Cells(iRow, 5).Value = Me.txtadcolumn.Value[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Cells(iRow, 6).Value = Me.txtcost.Value[/FONT][/COLOR]
[COLOR=#ff0000][FONT=Verdana].Cells(iRow, 7).Value = date1[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Cells(iRow, 8).Value = Me.txtrep.Value[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Cells(iRow, 9).Value = Me.txtsection.Value[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Cells(iRow, 10).Value = Me.txtspec.Value[/FONT][/COLOR]

[COLOR=#333333][FONT=Verdana]' .Protect Password:="password"[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]End With[/FONT][/COLOR]


[COLOR=#333333][FONT=Verdana]'clear the data[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Me.txtclient.Value = ""[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Me.txtname.Value = ""[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Me.txtphone.Value = ""[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Me.txtad.Value = ""[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Me.txtadcolumn.Value = ""[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Me.txtcost.Value = ""[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Me.txtdate.Value = ""[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Me.txtrep.Value = ""[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Me.txtsection.Value = ""[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Me.txtspec.Value = ""[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]End Sub[/FONT][/COLOR]


[COLOR=#333333][FONT=Verdana]Private Sub cmdClose_Click()[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Unload Me[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]End Sub[/FONT][/COLOR]
 
Last edited:
Upvote 0
Thank You, but this is driving me nuts it still reverts to m/dd/yy I Have changed the format to d/mm/yyyy;;"" and that doesnt work either any other thoughts please
 
Upvote 0
What date format is your Windows time in? mm-dd-yy?

I had this same issue for ages but since using that vba and changing my Windows date format to dd-mmm-yy my Userforms are working.

Adjust date/time / Change date & time formats / Short Date dd-mmm-yy


I also tested this by typing 13-01-18, on our format that's obviously Saturday, January 13, 2018.

When I changed the Windows time format back to mm/dd/yy it changed my cell to 01/13/18. So I think when you enter something that looks like a date into a textbox, excel calls the Windows date format before pasting to cell.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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