I have the below code set which saves calculation on a excel sheet but when the dates are saved, they are saved in US format (MM,DD,YYYY). I want to save the dates in UK format on the sheet from the following text boxes: txtFirstReadDate, txtSecondReadDate, txtRequiredDate.
Thank you
Private Sub UserForm_Initialize()
With Application
.WindowState = xlMaximized
Zoom = Int(.Width / Me.Width * 80)
Width = .Width
Height = .Height
End With
End Sub
Private Sub Calculate_Read_Button_Click()
If VBA.IsNumeric(Me.txtFirstRead.Value) = False Then
MsgBox "Please enter correct read"
Exit Sub
End If
If VBA.IsNumeric(Me.txtSecondRead.Value) = False Then
MsgBox "Please enter correct read"
Exit Sub
End If
If Me.txtFirstRead.Value = "" Or Me.txtSecondRead.Value = "" Then
MsgBox "Please fill in missing read field"
Exit Sub
End If
If VBA.IsDate(Me.txtFirstReadDate.Value) = False Then
MsgBox "Please enter correct date"
Exit Sub
End If
If VBA.IsDate(Me.txtSecondReadDate.Value) = False Then
MsgBox "Please enter correct date"
Exit Sub
End If
If VBA.IsDate(Me.txtRequiredDate.Value) = False Then
MsgBox "Please enter correct date"
Exit Sub
End If
If Me.txtFirstReadDate.Value = "" Or Me.txtSecondReadDate.Value = "" Then
MsgBox "Please fill in missing date field"
Exit Sub
End If
Me.txtReadDifference.Value = Val(txtSecondRead.Value) - Val(txtFirstRead.Value)
Me.txtDayDifference = DateDiff("d", txtFirstReadDate, txtSecondReadDate)
Me.txtR1_RequiredDate = DateDiff("d", txtFirstReadDate, txtRequiredDate)
Me.textUnitsperDay.Value = Val(txtReadDifference.Value) / Val(txtDayDifference.Value)
Me.txtUsedUnits.Value = Val(textUnitsperDay.Value) * Val(txtR1_RequiredDate.Value)
Me.txtRequiredRead.Value = Val(txtFirstRead.Value) + Val(txtUsedUnits.Value)
Me.txtRequiredRead = Format(txtRequiredRead.Value, "#,##")
End Sub
Private Sub Clear_Form_Button_Click()
Dim iControl As Control
For Each iControl In Me.Controls
If iControl.Name Like "txt*" Then iControl = vbNullString
Next
End Sub
Private Sub Delete_Records_Button_Click()
Dim i As Integer
For i = 1 To Range("A65356").End(xlUp).Row - 1
If ListDisplay.Selected(i) Then
Rows(i + 1).Select
Selection.Delete
End If
Next i
End Sub
Private Sub Exit_Button_Click()
Dim iExit As VbMsgBoxResult
iExit = MsgBox("Do youwant to Exit?", vbQuestion + vbYesNo, "Data Entry Form")
If iExit = vbYes Then
Unload Me
End If
End Sub
Private Sub Save_Records_Button_Click()
Dim wks As Worksheet
Dim AddNew As Range
Set wks = Sheet1
Set AddNew = wks.Range("A65356").End(xlUp).Offset(1, 0)
AddNew.Offset(0, 0).Value = txtMPRN.Text
AddNew.Offset(0, 1).Value = txtFirstRead.Text
AddNew.Offset(0, 2).Value = txtFirstReadDate.Text
AddNew.Offset(0, 3).Value = txtSecondRead.Text
AddNew.Offset(0, 4).Value = txtSecondReadDate.Text
AddNew.Offset(0, 5).Value = txtReadDifference.Text
AddNew.Offset(0, 6).Value = txtDayDifference.Text
AddNew.Offset(0, 7).Value = textUnitsperDay.Text
AddNew.Offset(0, 8).Value = txtR1_RequiredDate.Text
AddNew.Offset(0, 9).Value = txtUsedUnits.Text
AddNew.Offset(0, 10).Value = txtRequiredDate.Text
AddNew.Offset(0, 11).Value = txtRequiredRead.Text
ListDisplay.ColumnCount = 12
ListDisplay.RowSource = "A1:L65356"
End Sub
Private Sub UserForm1_Terminate()
ThisWorkbook.Close
End Sub
Bookmarks