UK and USA Date Format Issues - Userform

[Mousiefuzz]

This seems to be a really odd problem I'm having with userforms and dates. Specifically the American date format (mm/dd/yy) being used even though the pc and excel all runs on the UK date format (dd/mm/yy).

So here is my problem:

I have a button on a userform that generates a new ID number and the current date for new quotes.
At first when I used it, even though the date on my pc is 11/06/15 (dd/mm/yy), it was generating the date 06/11/15 (mm/dd/yy).
I found a fix to that by changing the date generation to:

txtQuotedat.Value = Format(Date, "dd/mm/yy")

This changed the date format back to 11/06/15 and when I saved the entry it wrote it to my spreadsheet as 11/06/15 (correct).

At this point everything seemed to be running smoothly.

However, I also have a button for finding quotes and repopulating all the data so you can go back at a later date and fill in the blanks.
When it finds the date from the quote on the spreadsheet and fills in the text box on the userform, it goes back to the american style 06/11/15.
When I press save this time it saves it to my spreadsheet as the american format.

Private Sub cmbQuote_Click()
    txtQuotenum = Cells(Rows.Count, "B").End(xlUp).Value + 1
    MsgBox ("Make sure Quote Numbers are sorted from smallest to largest to ensure a new Unique Contract Number is created!")
    txtQuotedat.Value = Format(Date, "dd/mm/yy")
End Sub

Does anyone have a fix for this? I need it to generate a UK format date from today's date when I press a certain button and also keep the correct format of date when pulling the date from the spreadsheet.

[Bernie Deitrick]

VBA is US-centric, whereas your worksheet is UK. When you save a date to the worksheet, make sure that your code is clear on how to convert the text to a valid date, along the lines of:

               Dim varD As Variant
            With ws
                .Cells(lRow, 2).Value = Me.txtQuotenum.Value
                varD = Split(Me.txtQuotedat.Value,"/")  'txtQuotedat is text in the form dd/mm/yy
                .Cells(lRow, 3).Value = DateSerial(cInt(varD(2)), cInt(varD(1)), cInt(varD(0))) 
                'etc.

[Mousiefuzz]

Took me a while to figure it out, still getting to grips with how to thread code together in Vba.
But eventually got this to work for both parts that I needed it to work for.

I'm still having a problem when it comes to updating though as I have two methods of saving data to my worksheet.
The first and the one that you have very helpfully fixed is my standard save. I input fresh information and the button writes it to the worksheet.

The second is a Find and Update combination which I can't figure out how to work that code into.
My Find button uses the Unique ID to grab all existing data up for that quote and the Update writes over the old information.
The Find button is putting the correct date on the userform textbox. The update is writing the USA format still.

How can I put this bit of code into my update to get it to work?
(It is txtStartdat1 and txtStartdat2 that I need this to work for)

Private Sub cmbUpdate_Click()
    Dim answer As Integer
    
        answer = MsgBox("Please use 'Save' if entering a new Contract, this feature is for correcting mistakes in data entry.", vbYesNo + vbQuestion, "Are you sure you want to Update?")
        If answer = vbYes Then
        
    Cost As String, Deposit As String, Start1 As String, PaymentL1 As String, PaymentP1 As String, Amount1 As String, Start2 As String, PaymentL2 As String
    
                Cost = txtCost.Value
            Cells(currentrow, 17).Value = Cost
            
                Deposit = txtDeposit.Value
            Cells(currentrow, 18).Value = Deposit
            
                Start1 = txtStartdat1.Text
            Cells(currentrow, 21).Value = Start1
            
                PaymentL1 = txtPayment1.Text
            Cells(currentrow, 22).Value = PaymentL1
            
                PaymentP1 = cbxPayment1.Text
            Cells(currentrow, 24).Value = PaymentP1
            
                Amount1 = txtAmount1.Value
            Cells(currentrow, 25).Value = Amount1
            
                Start2 = txtStartdat2.Text
            Cells(currentrow, 26).Value = Start2
            
                PaymentL2 = txtPayment2.Text
            Cells(currentrow, 27).Value = PaymentL2        
            
        Else
            'do nothing
        End If
    
End Sub

[romperstomper]

Assuming the dates are in the right format in the textboxes, just use CDate(txtStartdat1.Text) when writing to a cell.

[Mousiefuzz]

Unfortunately they aren't in the right format in the textboxes or at least this is where I'm having the issue in figuring out where it is converting the date format.
They are in the right 'order' in the text boxes, so when I type them out I type in 01/02/03 (1st Feb, 2003) but the vba is reading it as 01/02/03 (2nd Jan, 2003).

The other bit of code helped when saving so that it read it the correct way around (dd/mm/yy) but I don't know how to put that bit of code into my 'Update' button which writes over old information instead of saving to a blank row, my issue is no longer what code to use (I don't think so anyway) but just my ineptitude in understanding how it reads the code and how the code works and fits into my existing code.

[romperstomper]

If your date settings are typical UK regional, CDate("01/02/03") will return a true date value of 1 Feb 2003, so you just need:

activecell.value = CDate(txtStartdat1.Text)

for example.

[Mousiefuzz]

When I use that it is still returning the US date format.

My textbox contains "01/02/03" yet it is still inserting "02/01/03" in the worksheet.

[Norie]

How is the cell the date is going in formatted?

[Mousiefuzz]

Date - dd/mm/yy UK format

[Norie]

Which textbox(s)/date(s) are you having trouble with?

[romperstomper]

What exact code did you use? CDate always uses your regional settings to interpret date strings.

[Mousiefuzz]

Two pretty much identical text boxes, "Start Date *" and "Start Date"
http://i.imgur.com/946Fspr.png
The date is typed into those two text boxes which are named 'txtStartdat1' and 'txtStartdat2' respectively.

I type in the date "01/02/03" for example and when I press save, it saves that date as "01/02/03" in the correct cell on the worksheet.

However, when I go back into my userform to update incorrect information, I use my 'Update' button instead of the save button.
The difference between the two is one saves to a new line, the other saves over old information.
It is the 'Update' button that is saving the date wrong. The information is coming from the same text boxes but I don't know how to fit the code into my update button to make it format the date correctly.

This is the code I am currently using for my 'Update' button, specifically "txtStartdat1" and "txtStartdat2" are the ones that need formatting.

Private Sub cmbUpdate_Click()
    Dim answer As Integer
    
        answer = MsgBox("Please use 'Save' if entering a new Contract, this feature is for correcting mistakes in data entry.", vbYesNo + vbQuestion, "Are you sure you want to Update?")
        If answer = vbYes Then
        
    Cost As String, Deposit As String, Start1 As String, PaymentL1 As String, PaymentP1 As String, Amount1 As String, Start2 As String, PaymentL2 As String
    
                Cost = txtCost.Value
            Cells(currentrow, 17).Value = Cost
            
                Deposit = txtDeposit.Value
            Cells(currentrow, 18).Value = Deposit
            
                Start1 = txtStartdat1.Text
            Cells(currentrow, 21).Value = Start1
            
                PaymentL1 = txtPayment1.Text
            Cells(currentrow, 22).Value = PaymentL1
            
                PaymentP1 = cbxPayment1.Text
            Cells(currentrow, 24).Value = PaymentP1
            
                Amount1 = txtAmount1.Value
            Cells(currentrow, 25).Value = Amount1
            
                Start2 = txtStartdat2.Text
            Cells(currentrow, 26).Value = Start2
            
                PaymentL2 = txtPayment2.Text
            Cells(currentrow, 27).Value = PaymentL2        
            
        Else
            'do nothing
        End If
    
End Sub

As for the code I used, I tried entering:

Start1 = CDate(txtStartdat1.Text)
            Cells(currentrow, 21).Value = Start1

That into my 'Update' button. It didn't change the date format.

[Norie]

Are you using this for the two dates?

  Cells(currentrow, 21).Value = CDate(Start1)

  Cells(currentrow, 26).Value = CDate(Start2)

PS Why are you using variables for the values from the textboxes? Can't you just write directly from the textboxes to the cells?

  Cells(currentrow, 21).Value = CDate(txtStartdat1.Text)

  Cells(currentrow, 26).Value = CDate(txtStartdat2.Text)

[Mousiefuzz]

I think this is my current problem. I have no idea why I'm using variables, it was a piece of code I had some help with that I've used and that has worked... Up until now.

Should I in theory be able to rewrite all of that code instead as:

Private Sub cmbUpdate_Click()
    Dim answer As Integer
    
        answer = MsgBox("Please use 'Save' if entering a new Contract, this feature is for correcting mistakes in data entry.", vbYesNo + vbQuestion, "Are you sure you want to Update?")
        If answer = vbYes Then
        
       
                
            Cells(currentrow, 17).Value = txtCost.Value
                            
            Cells(currentrow, 18).Value = txtDeposit.Value
                             
            Cells(currentrow, 21).Value = txtStartdat1.Text
                             
            Cells(currentrow, 22).Value = txtPayment1.Text
                             
            Cells(currentrow, 24).Value = cbxPayment1.Text
                            
            Cells(currentrow, 25).Value = txtAmount1.Value
                             
            Cells(currentrow, 26).Value = txtStartdat2.Text
            
            Cells(currentrow, 27).Value = txtPayment2.Text      
            
        Else
            'do nothing
        End If

And if I did change it to that, how would I then insert that code to format the dates?

[romperstomper]

            Cells(currentrow, 21).Value = CDate(txtStartdat1.Text)

and the same for the other date textbox.

[Norie]

You would use the 2 lines of code I posted in the second section of code.

            Cells(currentrow, 17).Value = txtCost.Value
                            
            Cells(currentrow, 18).Value = txtDeposit.Value
                             
            Cells(currentrow, 21).Value = CDate(txtStartdat1.Text)
                             
            Cells(currentrow, 22).Value = txtPayment1.Text
                             
            Cells(currentrow, 24).Value = cbxPayment1.Text
                            
            Cells(currentrow, 25).Value = txtAmount1.Value
                             
            Cells(currentrow, 26).Value = CDate(txtStartdat2.Text)
            
            Cells(currentrow, 27).Value = txtPayment2.Text

[Mousiefuzz]

Okay, that's all working much better, date in correct format and it's also a lot tidier.

The only issue I'm having now is a run time error, type mismatch.
It only occurs when there is no second date (txtStartdat2 is blank) which will be a lot of the time. What can I add in to stop this?

[Norie]

Perhaps.

If Len(txtStartdat2.Text) <> 0 Then
    Cells(currentrow, 26).Value = CDate(txtStartdat2.Text)
End If

[Mousiefuzz]

You two have been invaluable help. Can't thank you both enough!