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:

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
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]
 

Visy123

New Member
Joined
Jul 13, 2018
Messages
11
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?
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
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:

Visy123

New Member
Joined
Jul 13, 2018
Messages
11
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
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
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.
 

Forum statistics

Threads
1,089,210
Messages
5,406,870
Members
403,110
Latest member
nataliemur

This Week's Hot Topics

Top