VBA Code to Format Text box problems

Drezy

New Member
Joined
Jun 25, 2014
Messages
4
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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")
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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