Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format returned from Userform to Excel worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format returned from Userform to Excel worksheet
Try changing this:
Range("C3").Select Selection.NumberFormat = "m/d/yyyy" To this: Range("C3") = Format(Range("C3").Value, "m/d/yyyy" ) "Lily" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format returned from Userform to Excel worksheet
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format returned from Userform to Excel worksheet
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format returned from Userform to Excel worksheet
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" wrote in message ... 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format returned from Userform to Excel worksheet
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format returned from Userform to Excel worksheet
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" wrote in message ... 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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format returned from Userform to Excel worksheet
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" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get a date to display in date format on a UserForm | Excel Programming | |||
Date Format in Userform | Excel Programming | |||
Date format in userform | Excel Programming | |||
I need today's date returned as date format in formula | Excel Discussion (Misc queries) | |||
Date format from UserForm | Excel Programming |