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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

RasGhul

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

ADVERTISEMENT

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
611
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,872
Messages
5,655,725
Members
418,234
Latest member
jdorfma

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
Top