VBA Code to Format Text box problems

[Drezy]

Hi,

I've spent the last 2 hours trying to find a answer to a problem I have. The issue I have is I have a form with 32 boxes, 2 of which are textboxes that contain a 'dd/mm/yy hh:mm' format. These boxes are populated from a excel spreadsheet with data at line level. However the code a have to send and changes to this data using the boxes on the form change the date format from dd/mm/yy hh:mm to mm/dd/yy hh:mm. If I open and search for the same data again and populate the boxes and save and it changes it back from mm/dd/yy to dd/mm/yy. I don't understand why its doing this.

cmdEdit is the button to save changes on the form.
reg3 is the textbox with the date issue

Here is the code I use to save / edit data within the form:

Private Sub cmdEdit_Click()
'Any data entered is updated and saved
'declare the variables
Dim findvalue As Range
Dim Ddate As Date
cNum = 32

Ddate = DateSerial(Year(Date), Month(Date), Day(Date))
Reg3.Value = Format(Reg3.Value, "dd/mm/yy hh:mm")
Ddate = Reg3.Value

'Reg16.Value = Format(Text)

'error handling
On Error GoTo errHandler:

'check for values
If Reg1.Value = "" Or reg2.Value = "" Then
MsgBox "There is not data to edit"
Exit Sub
End If

'edit the row
Set findvalue = ActiveSheet.Range("B:B").Find(What:=Reg1, LookIn:=xlValues).Offset(0, 0)

For x = 1 To cNum
findvalue = Me.Controls("Reg" & x).Value
Set findvalue = findvalue.Offset(0, 1)
Next
'Clear Data
For x = 1 To 13
Me.Controls("Reg" & x).Value = ""
Next
For x = 14 To 32
Me.Controls("Reg" & x).Value = ""
Next
'enable adding new data
Me.Reg1.Enabled = True
Me.reg2.Enabled = True
'Me.cmdAdd.Enabled = True
'refresh the listbox
Lookup
'error block
On Error GoTo 0
Exit Sub
errHandler:
MsgBox "An Error has Occurred " & vbCrLf & _
"The error number is: " & Err.Number & vbCrLf & _
Err.Description & vbCrLf & "Please notify the administrator"
End Sub

 

[pbornemeier]

What Regional setting is active on your system?
https://msdn.microsoft.com/en-us/library/ms268748(v=vs.100).aspx

The first paragraph in the above link says:
You must format all data that has locale-sensitive formatting, such as dates and currency, using the English (United States) data format (locale ID 1033) before you pass it to Microsoft Office Excel or read the data from code in your Office project.

Possibly change
findvalue = Me.Controls("Reg" & x).Value
to
findvalue = Format(Me.Controls("Reg" & x).Value, "mm/dd/yy hh:mm")