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

Try changing this:

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

To this:

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



"Lily" <(E-Mail Removed)> wrote in message
news:C72D607D-B2CF-4D6B-B905-(E-Mail Removed)...
> 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

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


--
Jacob


"Lily" wrote:

> 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

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.

--
Rick (MVP - Excel)



"Lily" <(E-Mail Removed)> wrote in message
news:C72D607D-B2CF-4D6B-B905-(E-Mail Removed)...
> 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

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

Lily

"JLGWhiz" wrote:

> Try changing this:
>
> Range("C3").Select
> Selection.NumberFormat = "m/d/yyyy"
>
> To this:
>
> Range("C3") = Format(Range("C3").Value, "m/d/yyyy" )
>
>
>
> "Lily" <(E-Mail Removed)> wrote in message
> news:C72D607D-B2CF-4D6B-B905-(E-Mail Removed)...
> > 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
>
>
> .
>

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

"Jacob Skaria" wrote:

> 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
>
>
> --
> Jacob
>
>
> "Lily" wrote:
>
> > 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

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

"Rick Rothstein" wrote:

> 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.
>
> --
> Rick (MVP - Excel)
>
>
>
> "Lily" <(E-Mail Removed)> wrote in message
> news:C72D607D-B2CF-4D6B-B905-(E-Mail Removed)...
> > 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
>
> .
>

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


"Rick Rothstein" wrote:

> 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.
>
> --
> Rick (MVP - Excel)
>
>
>
> "Lily" <(E-Mail Removed)> wrote in message
> news:C72D607D-B2CF-4D6B-B905-(E-Mail Removed)...
> > 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
>
> .
>