Date format in Userform

V

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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
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
RasGhul said:
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]
Click to expand...


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
You must log in or register to reply here.

Similar threads

E
  • Question Question
Generate unique code based on user form entry and put in table
2
Replies
18
Views
505
Micron
M
S
  • Question Question
User Form not Loading
Replies
5
Views
177
StevieMP
S
A
  • Question Question
Trying to add New names to a user list table with a user form. In the current format it does not show up on the table, what do I need to do?
Replies
1
Views
383
alleneure
A
K
  • Solved
VBA not finding next empty row due to formula
Replies
8
Views
1K
Alex Blakenburg
A
A
  • Question Question
Want to create new a new sheet based on user form input but cant get the new sheet to name correctly and just shows up as blank name.
Replies
1
Views
556
alleneure
A

Forum statistics

Threads
1,224,823
Messages
6,181,185
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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