Userform - Date format

[wrightyrx7]

Hi all,

I have to following code but for a userform. But the textbox for the data seems to output in onto the spreadsheet backwards - 01/09/2011 comes out on the worksheet like 09/01/2011

Can anyone help please?

 Private Sub cmdAdd_waiting_list_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Waiting List")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row


'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox1.Value
ws.Cells(iRow, 2).Value = Me.TextBox2.Value
ws.Cells(iRow, 3).Value = Me.TextBox3.Value
ws.Cells(iRow, 4).Value = Me.TextBox13.Value
ws.Cells(iRow, 5).Value = Me.TextBox11.Value
ws.Cells(iRow, 6).Value = Me.ComboBox1.Value
ws.Cells(iRow, 7).Value = Me.TextBox4.Value
ws.Cells(iRow, 8).Value = Me.TextBox5.Value
ws.Cells(iRow, 9).Value = Me.TextBox6.Value
ws.Cells(iRow, 10).Value = Me.TextBox7.Value
ws.Cells(iRow, 11).Value = Me.TextBox8.Value
ws.Cells(iRow, 12).Value = Me.TextBox9.Value
ws.Cells(iRow, 13).Value = Me.TextBox10.Value
ws.Cells(iRow, 14).Value = Me.TextBox12.Value
ws.Cells(iRow, 15).Value = Me.TextBox15.Value
ws.Cells(iRow, 16).Value = Me.TextBox16.Value
ws.Cells(iRow, 17).Value = Me.TextBox14.Value
ws.Cells(iRow, 18).Value = Me.TextBox17.Value
ws.Cells(iRow, 19).Value = Me.TextBox18.Value
ws.Cells(iRow, 20).Value = Me.TextBox19.Value
ws.Cells(iRow, 21).Value = Me.TextBox20.Value
ws.Cells(iRow, 22).Value = Me.TextBox25.Value
ws.Cells(iRow, 23).Value = Me.TextBox24.Value
ws.Cells(iRow, 24).Value = Me.ComboBox21.Value
ws.Cells(iRow, 25).Value = Me.TextBox22.Value
ws.Cells(iRow, 26).Value = Me.TextBox23.Value


'clear the data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.TextBox9.Value = ""
Me.TextBox10.Value = ""
Me.TextBox11.Value = ""
Me.TextBox12.Value = ""
Me.TextBox13.Value = ""
Me.TextBox14.Value = ""
Me.TextBox15.Value = ""
Me.TextBox16.Value = ""
Me.TextBox17.Value = ""
Me.TextBox18.Value = ""
Me.TextBox19.Value = ""
Me.TextBox20.Value = ""
Me.ComboBox21.Value = ""
Me.TextBox22.Value = ""
Me.TextBox23.Value = ""
Me.TextBox24.Value = ""
Me.TextBox25.Value = ""
Me.ComboBox1.Value = "Unknown"
Me.TextBox1.SetFocus
MsgBox ("Data Successfully added to Waiting List")


End Sub

'Initialize
Private Sub UserForm_Initialize()

    ComboBox1.List = Array("Mr", "Mrs", "Miss", "Ms", "Dr")
    ComboBox21.List = Array("Moston", "Openshaw", "External")
    Me.TextBox22.Text = Format(Date, "dd/mm/yyyy")
    Me.TextBox23.Text = Format(Date, "dd/mm/yyyy")
End Sub


'Calculations part 1
Private Sub TextBox17_Change()
            TextBox18_Change
End Sub

'Calculations part 2
Private Sub TextBox18_Change()
TextBox19.Enabled = False
TextBox20.Enabled = False
If IsNumeric(TextBox18.Value) Then
    TextBox19 = TextBox18.Value * 0.95
End If
If IsNumeric(TextBox17.Value) And _
    IsNumeric(TextBox19.Value) Then
    TextBox20 = TextBox19.Value * TextBox17.Value
End If
End Sub

 
Private Sub TextBox22_Enter()
    TextBox22 = ""
End Sub
 
Private Sub TextBox23_Enter()
    TextBox23 = ""
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Thanks
Chris

 

[Norie]

Not sure which textbox(es) you mean or where in the code but try using DateValue.

Range("A1").Value = DateValue(Me.TextBox22.Text)

 

[wrightyrx7]

oh im sorry, Textbox 22 & 23 are dates

 

[Norie]

Well you would sue DateValue with those when you are transferring their values to the worksheet.

That seems to be after ComboBox21 in the sub 'waiting_list_Click'.

 

[wrightyrx7]

Thanks Norie,

I tried it and i get the error "Method or Data member not found"

any suggestions?

Thanks

 

[Norie]

How exactly did you try it?

 

[wrightyrx7]

ws.Cells(iRow, 25).Value = Me.TextBox22.DateValue(Me.TextBox22.Text)
ws.Cells(iRow, 26).Value = Me.TextBox23.DateValue(Me.TextBox23.Text)

Could it be the other stuff further down in my code conflicting with it?

Sorry im really new to VBA

 

[Norie]

Just use the code I posted replacing Range("A1") with your range(s).

ws.Cells(iRow, 25).Value = DateValue(Me.TextBox22.Text)
ws.Cells(iRow, 26).Value = DateValue(Me.TextBox23.Text)

 

[wrightyrx7]

Worked great sorry for being a noob!! lol

I have posted another thread but no one seems to be intereseted. Dont suppose you can help with it. what i want to do is: -

I have a workbook with 10 worksheets i want to try and create a button to do the following.

- save copy of active worksheet.
- open the save as box of a specified directory
- default save name as Cells - A9 " " B7 " " E2.xls


any help or advice would be great

thanks
chris

 

[Norie]

Chris

Can you post a link for the thread?

In the meantime can I just clarify a couple of things.

1 Do you mean create a copy of the activesheet within the same workbook?

3 Is the filename taken from those cells in the worksheet being copied?

Apologies if they've already been asked, but I haven't seen the other thread after all.