0

I'm new in VBA, And I need help in a problem regarding the date format when is transferred from textbox to the cells is changing from dd/mm/yyyy to mm/dd/yyyy.

This is the program that I create using a user form as interface in insert the data. My problem if I can not maintain the dd/mm/yyyy format when I transfer to the cells.

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("hocregister")
Set ws = Workbooks("Construction HOC register.xlsm").Worksheets("hocregister")


'revised code to avoid problems with Excel tables in newer versions
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1



'copy the data to the database
ws.Cells(iRow, 2).Value = Me.txthocdate.Value
Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
catalindemidov
  • 1
  • 1
  • 1
    Your two `Set ws = ...` lines are redundant. Eliminate the first one, as the second is more specific. – FreeMan Oct 03 '18 at 12:41
  • Also, you'll find your answer in the accepted answer to [this question](https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2) about the difference between `.text`, `.value` and `.value2`. – FreeMan Oct 03 '18 at 12:43
  • If `Construction HOC register.xlsm` is the file containing the code you can change it to `ThisWorkbook.Worksheets("hocregister")`. - `ThisWorkbook` is _always_ the file containing the code that's running (so you can change the name of your file without the whole thing breaking). – Darren Bartrup-Cook Oct 03 '18 at 14:58

1 Answers1

0

Your form doesn't know the format of the values entered into a textbox, so you need to make it explicit when you copy the value to your sheet.

Change to ws.Cells(iRow, 2).Value = CDATE(Me.txthocdate.Value) to convert the value in txthocdate to a date.

Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45