+ Reply to Thread
Results 1 to 5 of 5

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

Hybrid View

  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

  2. #2
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,411

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

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,021

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

    Use CDate:

    AddNew.Offset(0, 2).Value = CDate(txtFirstReadDate.Text)
    for example.
    Everyone who confuses correlation and causation ends up dead.

  4. #4
    Registered User
    Join Date
    09-16-2020
    Location
    UK
    MS-Off Ver
    Latest
    Posts
    2

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

    Perfectly works!
    Thank you

  5. #5
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,021

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

    Glad to help.

+ Reply to Thread

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