Day and month switching around in Excel/Vba

  oo7juk 12:04 02 Apr 2008
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 2008

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 2008

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

  xania 09:47 03 Apr 2008

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

  oo7juk 10:40 03 Apr 2008

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 2008

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 2008

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 2008

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 2008

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 2008

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 2008

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.

Elsewhere on IDG sites

OnePlus 5 review

See the work of famous artists playing with toys

iPad Pro 10.5in (2017) review

Comment faire une capture d’écran sur un Mac ?