Date Format returned from Userform to Excel worksheet

[Lily]

Hi,

I have a routine vb where I have a form (RXWfrmDate) with the date
(m/d/yyyy) for the user to pick a date for download to specific cell on the
worksheet.

Sub RXWDate()
Dim DateIN As Date
Application.ScreenUpdating = False
RXWfrmDATE.Show
If frmCANCEL = True Then Exit Sub
ActiveWorkbook.Unprotect
Sheets("Reactor Water").Select
ActiveSheet.Unprotect
Range("C3") = DateIN
Range("C3").Select
Selection.NumberFormat = "m/d/yyyy"
Exit Sub
Application.ScreenUpdating = True
End Sub

However, the date that shows on Range "C3") after user input is 01/00/1900.

What am I missing?

Appreciate any help.

Thanks.

Lily

 

[JLGWhiz]

Try changing this:

Range("C3").Select
Selection.NumberFormat = "m/d/yyyy"

To this:

Range("C3") = Format(Range("C3").Value, "m/d/yyyy" )

 

[Jacob Skaria]

Try replacing

Sheets("Reactor Water").Select
ActiveSheet.Unprotect
Range("C3") = DateIN
Range("C3").Select
Selection.NumberFormat = "m/d/yyyy"

with

With Sheets("Reactor Water")
.Unprotect
.Range("C3") = DateValue(datein)
.Range("C3").NumberFormat = "m/d/yyyy"
End With

 

[Rick Rothstein]

I think the problem is one of variable scope. You have DateIN declared as a
Date inside your RXWDate subroutine, but I'm guessing this value is being
set in your UserForm and I further guessing that you have a DateIN variable
declared in the UserForm's code somewhere as well. If I am right, then the
problem is each of those declarations are local to the code procedure they
are in and, as such, know nothing about each other. Try this... remove the
DateIN declaration from both locations, add a Module to your project
(Insert/Module from the VB Editor menu bar) and put your declaration for
DateIN in the Module's code window (doing this will make the variable
visible to all code modules within your project). Now see if your code works
as you expect it to.

 

[Lily]

Thanks. I tried your suggestions. When I inputted 2/2/2002 I got
12/30/1899 on Range C3.

Lily

 

[Lily]

Thanks Jacob. I tried your suggestion and still got 1/0/1900 on Range C3.
Lily

 

[Lily]

Thanks Rick. Now I am getting frustrated.
You are correct I declared DateIN As Date in both places (Userform & Module
level). I deleted Dim DateIn As Date from both places. I added a Module
with Declaration Dim DateIN as Date.

No date was shown on Range C3 after inputting 2/2/2002.

Lily

 

[Lily]

Rick,

I did another check on the Declaration and it worked.

1. I made sure that the declarations for DateIN are deleted from the form &
the RXWDate Module.
2. I made sure that my Range C3 is Named As DateIN.
2. I made a Global Declaration of the DateIn As Date in my new Module.

Now I get the correct date/s I inputted on the form

Thanks Rick. Also thanks to all who sent suggestions.

Lily