Date Format returned from Userform to Excel worksheet

L

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
 
J

JLGWhiz

Try changing this:

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

To this:

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

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
 
R

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.
 
L

Lily

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

Lily
 
L

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
 
L

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top