Excel Date formatting problem

[simora]

I am entering a date from an excel userform into 2 worksheets. No matter how I enter the date, it always displays Saturday, January 00, 1900 into the 2nd spreadsheet. The 1st sheet displays correctly.

How can I format either the sheet or the VBA code so that it correctly enters the date entered on the User Form.

Thanks

[GTO]

On a blank/new worksheet, type '91/1/11' in the formula bar and click the checkmark left of where you enter data in the formula bar. It should change to '9/1/2011'. Good so far?

Okay - show us the userform (or replicate it) so we can see how you are coercing the string returned from the text/combo/list box.

[simora]

RE: It should change to '9/1/2011'. Good so far?

Actually NO ! It did not change to 9/1/2011

I'm shrinking the code before posting to see what happens.

Thanks

[simora]

I tried to shrink the form code & I cant replicate the problem on the smaller form.

This is the date information & code I'm using.

Form code:



Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox6 = Format(TextBox6, "DDDD, mmmm. dd, yyyy")
End Sub

Private Sub CommandButton2_Click()
strTime = Time
strDate = Date
Set c = Range("a65536").End(xlUp).End(xlUp).Offset(1, 0)
'write userform date value
c.Value = Me.TextBox6.Value ' Date

If CheckBox4 = True Then
c.Offset(0, 36).AddComment
c.Offset(0, 36).Comment.Visible = False
c.Offset(0, 36).Comment.Text Text:=strTime & ":" & Chr(10) & strDate & Chr(10) & "" & "" & ComboBox2.Value
c.Offset(0, 36).Comment.Shape.TextFrame.AutoSize = True

Else
c.Offset(0, 36).Value = ""
End If

ThisWorkbook.Sheets(5).Select
Set c = ThisWorkbook.Sheets(5).Range("a2")
'write userform date value
c.Value = Me.TextBox6.Value ' Date
c.Offset(0, 1).Value = Me.TextBox7.Value ' Name

ThisWorkbook.Sheets(1).Select

End Sub

[kirbz]

Hi Simora,

Have you tried to check if the Cell Format of your 2nd Sheet is in Date Format?

[simora]

Yup! And I tried re-formatting a new sheet. Same results.
I'm totally stumped.

[GTO]

RE: It should change to '9/1/2011'. Good so far?

Actually NO ! It did not change to 9/1/2011

I'm shrinking the code before posting to see what happens.

Thanks

What exactly does it do when entered in the formula bar?

Can you attach the workbook?

[simora]

No matter how I enter the date, it always displays Saturday, January 00, 1900 into the 2nd spreadsheet in Col A. The 1st sheet displays correctly.

When I enter your suggestion into the formula bar, nothing changes.
There are 15 sheets with lots of links and dependencies etc..etc.. that's why I tried to shrink anything that may have something to do with dates and formatting. I'll try to attach the sheets in some form but its really very large.

I use these 2 vars at the top of the code which submits the form.
Never had a problem until NOW. The problem is only happening after I select the second worksheet . Everything else there works fine.

[kirbz]

Hi Simora,

I tried your code using microsoft excel 2007, using sheet1 and sheet2.. it seems its working fine.. it converts the '9/1/2011' to 'Thursday, September. 01, 2011' on both sheets 1 and 2.. although i did not use the option regarding the checkbox and combobox..
what version are u using? or have u tried your file on another PC?

Im not sure because im juz a newbie.. juz trying to help..

anyways goodluck.. :-)

[simora]

kirbz (http://www.vbaexpress.com/forum/member.php?u=38886):

Thanks.

I am using Office 2003.
I finally said that it was way too much time for the end result.
{ Sometimes this happens. }
I can't decode the amount of code involved to try to solve this issue right now, but thanks to everyone who gave it consideration.

When time permits I will revisit the issue and post the finding if I solve this pesky problem.

Again THANKS !

[GTO]

Hi Simora,

If the data is not sensitive, you could zip the wb and post it as is. The worse we might do is not spot it as well.

Mark

[simora]

Thanks Mark:

Turns out that I had a set of hidden worksheets that I had forgotten about from when I cloned the workbook, and there was a DATE reference to another cell on a hidden sheet that was called just before I selected the Sheet(5) where the problem date was being posted.
' TextBox6.Value = ThisWorkbook.Sheets(22).Range("AA2")
Unfortunately, this cell has an old formula inside.

In case anyone else has a problem almost like this, maybe this other suggestion from another source helps.

IF 1904 Date System is already UNchecked. &
If Date defaults to 01/00/1900
Try the following

Tools > Options > Transition

untick the boxes for "transition formula evaluation" and "transition formula entry".

Thanks for all your consideration.

[GTO]

Well kudos and glad you got it fixed :-)