Day and month switching around in Excel/Vba

  oo7juk 12:04 02 Apr 08
Locked

Hi,

I have created a userform that contains texts boxes and command buttons. When I enter a date e.g. 01/04/2008 via the userform it displays as 04/01/2008 on the worksheet. I have tried to format the cells, but it won't let me. I have also checked my regional settings and they are OK.

Is it possible to enter the data as 010408 and display as 01/04/08 on the worksheet.

Many thanks.

  xania 15:37 02 Apr 08

Click on any cell in the spreadsheet then go to the Format drop down menu and select <Cell>. Now, on the <Number> tab select date and check the locale box shows UK.

  oo7juk 16:43 02 Apr 08

Hi, that box is already ticked as UK. Still doesn't work I'm afraid, thanks.

  xania 09:47 03 Apr 08

Can you give us the VB code. We might be able to spot something or develop a work-around.

  oo7juk 10:40 03 Apr 08

Hi - no problem

Private Sub CommandButton1_Click()
Dim LastRow As Object

Set LastRow = Sheet1.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = ComboBox1.Value
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = ComboBox2.Value
LastRow.Offset(1, 4).Value = TextBox3.Text

MsgBox "One record written to Sheet1"

response = MsgBox("Do you want to enter another record?", _
vbYesNo)

If response = vbYes Then
ComboBox1.Value = ""
TextBox2.Text = ""
ComboBox2.Value = ""
TextBox3.Text = ""

ComboBox1.SetFocus

Else
Unload Me
End If

End Sub

  VoG II 11:54 03 Apr 08

I'm not sure which of your TextBoxes is used for the date but try code like this

Dim mydate As Date
mydate = TextBox1.Value
Sheets("Sheet1").Range("A1").Value = mydate

  oo7juk 13:10 03 Apr 08

VoG,

I inserted the code above the following line -

MsgBox "One record written to Sheet1"

and it insertd the correct date format in cell A1, but also put the incorrect format in cell B2.

Sheet1 has headers name,date etc. Previously when I clicked the command button it would put the name in a2 and the date in B2.

I changed the range from A1 to A65536, but it never worked.

Thanks.

  VoG II 13:47 03 Apr 08

I don't quite understand but for each date in a TextBox you will need to implicitly change it to a date using

mydate = TextBox1.Value

before writing the value to the sheet.

  oo7juk 14:50 03 Apr 08

The following code is writing the correct value to cell A and the wrong value to cell B2. I want it to write the correct value to column B apart from B1 which contains the title of the column.

Thanks,

Private Sub CommandButton1_Click()
Dim LastRow As Object

Set LastRow = Sheet1.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = ComboBox1.Value
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = ComboBox2.Value
LastRow.Offset(1, 4).Value = TextBox3.Text
Dim mydate As Date
mydate = TextBox2.Value
Sheets("Sheet1").Range("A1").Value = mydate

MsgBox "One record written to Sheet1"

response = MsgBox("Do you want to enter another record?", _
vbYesNo)

If response = vbYes Then
ComboBox1.Value = ""
TextBox2.Text = ""
ComboBox2.Value = ""
TextBox3.Text = ""

ComboBox1.SetFocus


Else
Unload Me
End If

End Sub

  VoG II 15:02 03 Apr 08

Try

Private Sub CommandButton1_Click()
Dim LastRow As Object, mydate As Date

'Changed this as well so it will also work in XL 2007
Set LastRow = Sheet1.Range("A" & Rows.Count).End(xlUp)

LastRow.Offset(1, 0).Value = ComboBox1.Value
mydate = TextBox2.Value
LastRow.Offset(1, 1).Value = mydate
LastRow.Offset(1, 2).Value = ComboBox2.Value
LastRow.Offset(1, 4).Value = TextBox3.Text

MsgBox "One record written to Sheet1"

response = MsgBox("Do you want to enter another record?", _
vbYesNo)

If response = vbYes Then
ComboBox1.Value = ""
TextBox2.Text = ""
ComboBox2.Value = ""
TextBox3.Text = ""

ComboBox1.SetFocus


Else
Unload Me
End If

End Sub

  oo7juk 22:53 03 Apr 08

Thanks - is it possible to enter data on userform as 010408 rather than 01/04/08 and for the value to be written as 01/04/08 on the sheet, thanks

This thread is now locked and can not be replied to.

Sniper Elite 4 review: Headshotting Nazis has never felt so good

1995-2015: How technology has changed the world in 20 years

The Fresh New Fonts of 2017

WWDC 2017 dates: How to get WWDC 2017 tickets, when is WWDC 2017 and more details announced