Results 1 to 5 of 5

Date saved from userform to cell in US form. How to force save it in UK form?

Threaded View

4one4 Date saved from userform to... 09-16-2020, 08:58 AM
torachan Re: Date saved from userform... 09-16-2020, 09:14 AM
romperstomper Re: Date saved from userform... 09-16-2020, 10:03 AM
4one4 Re: Date saved from userform... 09-16-2020, 07:46 PM
romperstomper Re: Date saved from userform... 09-17-2020, 04:20 AM
  1. #1
    Registered User
    Join Date
    09-16-2020
    Location
    UK
    MS-Off Ver
    Latest
    Posts
    2

    Date saved from userform to cell in US form. How to force save it in UK form?

    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
    Last edited by 4one4; 09-16-2020 at 07:54 PM. Reason: Solved

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. need to save user-form as a form format
    By Rasika Manohari in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-14-2017, 12:21 PM
  2. [SOLVED] Create a form with a button to save the form and open Outlook
    By OmegaSea in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-03-2015, 05:12 PM
  3. Excel 2010 - VBA Userform - How to open a form starting at the top of the form
    By theshybutterfly in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-29-2015, 05:45 AM
  4. TRnasferring date form userform textbox to cell in worksheet
    By chrisjames25 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-27-2014, 07:57 AM
  5. how to save records through Data entry form
    By roofi in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-28-2013, 09:01 AM
  6. [SOLVED] .SHOW Form to auto-save PDF of completed form
    By jonvanwyk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-06-2012, 09:56 AM
  7. User Form to save Form entries & check for duplicates
    By KPK in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-01-2010, 05:44 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1