Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Forms and Date Formats
Hi there,
I have two problems with some user forms i am trying to create. Firstly the following code takes the entries from the data form into a worksheet ws.Cells(iRow, 2).Value = Me.txtstdt.Value ws.Cells(iRow, 3).Value = Me.txtendt.Value both txtstdt and txtendt are dates which are to be dd/mm/yy, however appear on my worksheet as mm/dd/yy. Can anyone help me format them correctly? The code appears to overwrite the cell formatting. Secondly I have a job number layed out as 12-3456 which is entered into a form and this code puts it into another worksheet ws.Cells(iRow, 1).Value = Me.txtjobno.Value My problem here is that it formats it as a date even though my worksheet formattin is and should be set to general. Any ideas very welcome! Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Forms and Date Formats
with ws.Cells(iRow, 2)
'just an unambiguous date for testing, change it when you're happy it's ok .numberformat = "mmmm dd, yyyy" 'cdate() uses the windows setting (mdy order) for the user 'and that may not be what you (the developer) expects! .Value = cdate(Me.txtstdt.Value) end with And I'd just format that other cell as text first. with ws.Cells(iRow, 1) .numberformat = "@" .Value = Me.txtjobno.Value end with or start the entry with an apostrophe: ws.Cells(iRow, 1).Value = "'" & Me.txtjobno.Value so that it would be entered as text. Mustang wrote: Hi there, I have two problems with some user forms i am trying to create. Firstly the following code takes the entries from the data form into a worksheet ws.Cells(iRow, 2).Value = Me.txtstdt.Value ws.Cells(iRow, 3).Value = Me.txtendt.Value both txtstdt and txtendt are dates which are to be dd/mm/yy, however appear on my worksheet as mm/dd/yy. Can anyone help me format them correctly? The code appears to overwrite the cell formatting. Secondly I have a job number layed out as 12-3456 which is entered into a form and this code puts it into another worksheet ws.Cells(iRow, 1).Value = Me.txtjobno.Value My problem here is that it formats it as a date even though my worksheet formattin is and should be set to general. Any ideas very welcome! Thanks -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Forms and Date Formats
Thank you for your response.
I have tried the following (my VB knowledge is minimal)... ws.Cells(iRow, 2).Value = Me.txtstdt.Value With ws.Cells(iRow, 2).NumberFormat = "mm,dd,yyyy" End With ws.Cells(iRow, 3).Value = Me.txtendt.Value With ws.Cells(iRow, 3).NumberFormat = "mm,dd,yyyy" End With and it still does not work. I am not sure I have put the code in correctly. Thanks "Dave Peterson" wrote: with ws.Cells(iRow, 2) 'just an unambiguous date for testing, change it when you're happy it's ok .numberformat = "mmmm dd, yyyy" 'cdate() uses the windows setting (mdy order) for the user 'and that may not be what you (the developer) expects! .Value = cdate(Me.txtstdt.Value) end with And I'd just format that other cell as text first. with ws.Cells(iRow, 1) .numberformat = "@" .Value = Me.txtjobno.Value end with or start the entry with an apostrophe: ws.Cells(iRow, 1).Value = "'" & Me.txtjobno.Value so that it would be entered as text. Mustang wrote: Hi there, I have two problems with some user forms i am trying to create. Firstly the following code takes the entries from the data form into a worksheet ws.Cells(iRow, 2).Value = Me.txtstdt.Value ws.Cells(iRow, 3).Value = Me.txtendt.Value both txtstdt and txtendt are dates which are to be dd/mm/yy, however appear on my worksheet as mm/dd/yy. Can anyone help me format them correctly? The code appears to overwrite the cell formatting. Secondly I have a job number layed out as 12-3456 which is entered into a form and this code puts it into another worksheet ws.Cells(iRow, 1).Value = Me.txtjobno.Value My problem here is that it formats it as a date even though my worksheet formattin is and should be set to general. Any ideas very welcome! Thanks -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Forms and Date Formats
Try using the suggested code as written.
Just replace your existing single line with that group of 4. Then it's almost the same for the second textbox. Mustang wrote: Thank you for your response. I have tried the following (my VB knowledge is minimal)... ws.Cells(iRow, 2).Value = Me.txtstdt.Value With ws.Cells(iRow, 2).NumberFormat = "mm,dd,yyyy" End With ws.Cells(iRow, 3).Value = Me.txtendt.Value With ws.Cells(iRow, 3).NumberFormat = "mm,dd,yyyy" End With and it still does not work. I am not sure I have put the code in correctly. Thanks "Dave Peterson" wrote: with ws.Cells(iRow, 2) 'just an unambiguous date for testing, change it when you're happy it's ok .numberformat = "mmmm dd, yyyy" 'cdate() uses the windows setting (mdy order) for the user 'and that may not be what you (the developer) expects! .Value = cdate(Me.txtstdt.Value) end with And I'd just format that other cell as text first. with ws.Cells(iRow, 1) .numberformat = "@" .Value = Me.txtjobno.Value end with or start the entry with an apostrophe: ws.Cells(iRow, 1).Value = "'" & Me.txtjobno.Value so that it would be entered as text. Mustang wrote: Hi there, I have two problems with some user forms i am trying to create. Firstly the following code takes the entries from the data form into a worksheet ws.Cells(iRow, 2).Value = Me.txtstdt.Value ws.Cells(iRow, 3).Value = Me.txtendt.Value both txtstdt and txtendt are dates which are to be dd/mm/yy, however appear on my worksheet as mm/dd/yy. Can anyone help me format them correctly? The code appears to overwrite the cell formatting. Secondly I have a job number layed out as 12-3456 which is entered into a form and this code puts it into another worksheet ws.Cells(iRow, 1).Value = Me.txtjobno.Value My problem here is that it formats it as a date even though my worksheet formattin is and should be set to general. Any ideas very welcome! Thanks -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Forms and Date Formats
That is great, thanks very much for your help. "Dave Peterson" wrote: Try using the suggested code as written. Just replace your existing single line with that group of 4. Then it's almost the same for the second textbox. Mustang wrote: Thank you for your response. I have tried the following (my VB knowledge is minimal)... ws.Cells(iRow, 2).Value = Me.txtstdt.Value With ws.Cells(iRow, 2).NumberFormat = "mm,dd,yyyy" End With ws.Cells(iRow, 3).Value = Me.txtendt.Value With ws.Cells(iRow, 3).NumberFormat = "mm,dd,yyyy" End With and it still does not work. I am not sure I have put the code in correctly. Thanks "Dave Peterson" wrote: with ws.Cells(iRow, 2) 'just an unambiguous date for testing, change it when you're happy it's ok .numberformat = "mmmm dd, yyyy" 'cdate() uses the windows setting (mdy order) for the user 'and that may not be what you (the developer) expects! .Value = cdate(Me.txtstdt.Value) end with And I'd just format that other cell as text first. with ws.Cells(iRow, 1) .numberformat = "@" .Value = Me.txtjobno.Value end with or start the entry with an apostrophe: ws.Cells(iRow, 1).Value = "'" & Me.txtjobno.Value so that it would be entered as text. Mustang wrote: Hi there, I have two problems with some user forms i am trying to create. Firstly the following code takes the entries from the data form into a worksheet ws.Cells(iRow, 2).Value = Me.txtstdt.Value ws.Cells(iRow, 3).Value = Me.txtendt.Value both txtstdt and txtendt are dates which are to be dd/mm/yy, however appear on my worksheet as mm/dd/yy. Can anyone help me format them correctly? The code appears to overwrite the cell formatting. Secondly I have a job number layed out as 12-3456 which is entered into a form and this code puts it into another worksheet ws.Cells(iRow, 1).Value = Me.txtjobno.Value My problem here is that it formats it as a date even though my worksheet formattin is and should be set to general. Any ideas very welcome! Thanks -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Forms | Excel Programming | |||
User forms help | Excel Programming | |||
Text, Number and Date formats excel 2003 driving experienced user | Excel Discussion (Misc queries) | |||
User Forms | Excel Programming | |||
User Forms - Max Me | Excel Programming |