I have created a Userform to accept a date input (8 characters) in the form
of mm/dd/yy. When the data is transferred to the spreadsheet, the display
show mm/dd/yy but the content is changed to mm/dd/yyyy. I need the content
to stay at mm/dd/yy. Any suggestions?

Thanks!

Hi

- if you mean the cell itself displays mm/dd/yyyy then it would mean you
sent the data to the cell but didn't format the cell, in which case you would
need to do something like:
Dim rg As Range
Set rg = Range("A1")

rg = CDate(TextBox1) 'assign date value
rg.NumberFormat = "mm/dd/yy" 'format th value
- if by content you mean the number in the formula bar (mm/dd/yyyy).. while
the displayed date is the cell itself is mm/dd/yy, then i don't think there
is much to be done (assuming you want to keep the data as date, otherwise you
could still format the cell as text).
But maybe i didn't understand your question correctly.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"leung747" wrote:

I have created a Userform to accept a date input (8 characters) in the form
of mm/dd/yy. When the data is transferred to the spreadsheet, the display
show mm/dd/yy but the content is changed to mm/dd/yyyy. I need the content
to stay at mm/dd/yy. Any suggestions?

Thanks!

Thank you for responding. I am in the second scenario where I see the cell
as 08/05/05; but when I click on the cell itself, the formula bar shows the
data as 08/05/2005. Previously, we manually enter the data into the cell by
adding an apostrophy in front of the data (e.g. '08/05/05), and then it will
display as 08/05/05 and when I click on the cell, the formula bar content
will have '08/05/05. I am trying to accomplish this by having the user input
the data via the userform instead of entering it directly on the spreadsheet.
The reason I need the date to stay as 08/05/05 is because after the user
enter the data into the spreadsheet, my macro continues on and transfer the
data onto our mainframe application, makes the calculation, and then collects
the result back onto the spreadshhet. The mainframe application can only
handles date format as 08/05/05 and not 08/05/2005.

Any suggestions to accomplish this? Thanks! Ed



"sebastienm" wrote:

Hi

- if you mean the cell itself displays mm/dd/yyyy then it would mean you
sent the data to the cell but didn't format the cell, in which case you would
need to do something like:
Dim rg As Range
Set rg = Range("A1")

rg = CDate(TextBox1) 'assign date value
rg.NumberFormat = "mm/dd/yy" 'format th value
- if by content you mean the number in the formula bar (mm/dd/yyyy).. while
the displayed date is the cell itself is mm/dd/yy, then i don't think there
is much to be done (assuming you want to keep the data as date, otherwise you
could still format the cell as text).
But maybe i didn't understand your question correctly.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"leung747" wrote:

I have created a Userform to accept a date input (8 characters) in the form
of mm/dd/yy. When the data is transferred to the spreadsheet, the display
show mm/dd/yy but the content is changed to mm/dd/yyyy. I need the content
to stay at mm/dd/yy. Any suggestions?

Thanks!

Do you know how the steps of the macro sending the data to the mainframe? Do
you have access to the code?


If the date is the only issue anhd you don;t use the date in computations
later on, you may want to format the cell as Text so that the sent value is
not 'modified'. Assuming the date is sent to cell A1 from textbox1.
Range("a1").numberFOrmat= "@" 'format as text
Range("a1")=format(textbox1,"mm/dd/yy") 'send formatted text value
Now the cell has the formatted date as you sent it, but as text.


I remember doing something similar long ago where i had to export as a text
file. I rememeber i could not just save the book as csv or regular text file.
To prevent any auto-modification of the shape of the data, I was reformatting
so that the database file import would recognize the fields. I remember doing
something like:
- say data is in sheet 1, spanning A2 to C100 with field1 is Text , field
2 is Date, field 3 is text.
- in hidden sheet 2:
-in A1, i enter the formula: =sheet1!a2 & "|" &
TEXT(sheet1!b2,"mm/dd/yy") & sheet1!c2
(where | was by field separator)
- then copy sheet2!a1 and paste it down to A99
Now sheet2 contains a formatted version of sheet1 in 1 single column, eg:
A1: MyName|02/02/05|MyAge
Finally i would export that column to the server.

I hope this helps,
--
Regards,
Sébastien
<http://www.ondemandanalysis.com

Hi Sebastien,

Thank you for your quick response and great interest in resolving my
problem. The following is the code from my macro to transfer the data in the
cell to our mainframe session.

S_Date = ActiveSheet.Cells((Row - 1), 2).Value 'Start Date
MyScreen.PutString S_Date, 8, 44

I will try your suggestion and will let you know of my results. thanks! Ed


"sebastienm" wrote:

Do you know how the steps of the macro sending the data to the mainframe? Do
you have access to the code?


If the date is the only issue anhd you don;t use the date in computations
later on, you may want to format the cell as Text so that the sent value is
not 'modified'. Assuming the date is sent to cell A1 from textbox1.
Range("a1").numberFOrmat= "@" 'format as text
Range("a1")=format(textbox1,"mm/dd/yy") 'send formatted text value
Now the cell has the formatted date as you sent it, but as text.


I remember doing something similar long ago where i had to export as a text
file. I rememeber i could not just save the book as csv or regular text file.
To prevent any auto-modification of the shape of the data, I was reformatting
so that the database file import would recognize the fields. I remember doing
something like:
- say data is in sheet 1, spanning A2 to C100 with field1 is Text , field
2 is Date, field 3 is text.
- in hidden sheet 2:
-in A1, i enter the formula: =sheet1!a2 & "|" &
TEXT(sheet1!b2,"mm/dd/yy") & sheet1!c2
(where | was by field separator)
- then copy sheet2!a1 and paste it down to A99
Now sheet2 contains a formatted version of sheet1 in 1 single column, eg:
A1: MyName|02/02/05|MyAge
Finally i would export that column to the server.

I hope this helps,
--
Regards,
Sébastien
<http://www.ondemandanalysis.com

I would use the Text property of the range instead of the Value. The Value is
the internal value (visible in the address bar... most of the time) which may
or may not be a string, while the Text is the string being seen in the cell.
S_Date = ActiveSheet.Cells((Row - 1), 2).Text

By curiousity, what library are you using? What is type of object is MyScreen?
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"leung747" wrote:

Hi Sebastien,

Thank you for your quick response and great interest in resolving my
problem. The following is the code from my macro to transfer the data in the
cell to our mainframe session.

S_Date = ActiveSheet.Cells((Row - 1), 2).Value 'Start Date
MyScreen.PutString S_Date, 8, 44

I will try your suggestion and will let you know of my results. thanks! Ed

Hi Sebastienm,

You suggestion worked like a charm. Thank you very much for your
assistance. To satisfy your curiousity, I have to use the following line
codes to make it work:
Dim Sessions As Object
Dim System As Object
Dim MyScreen As Object
Dim Sess As Object
Set System = CreateObject ("Extra.System")
Dim Sess0 As Object
Set Sess0 = System.ActiveSession
Set MyScreen = Sess0.Screen.

Not sure if I need all or what each one does, but that's how it works. Once
again, thank you for your assistance in resolving my problem.

Aloha from Hawaii,

Ed


"sebastienm" wrote:

I would use the Text property of the range instead of the Value. The Value is
the internal value (visible in the address bar... most of the time) which may
or may not be a string, while the Text is the string being seen in the cell.
S_Date = ActiveSheet.Cells((Row - 1), 2).Text

By curiousity, what library are you using? What is type of object is MyScreen?
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"leung747" wrote:

Hi Sebastien,

Thank you for your quick response and great interest in resolving my
problem. The following is the code from my macro to transfer the data in the
cell to our mainframe session.

S_Date = ActiveSheet.Cells((Row - 1), 2).Value 'Start Date
MyScreen.PutString S_Date, 8, 44

I will try your suggestion and will let you know of my results. thanks! Ed