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

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

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

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

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