Is there a way so that when my sheet is opened up the first time NOW will put
in the correct date in one cell and the time in another but when it is open
again the time wont change.

The sheet we use is sent to an individual and they inturn open it, copy and
paste rows to a master sheet they have (they wont use ACCESS which I think
would be easier). The sheet we send we do not keep so we have the sheet as an
XLT (2003 is our version) so someone doesn't accidently save and mess up our
master copy.

Column "A" is date sent, Column "B" is time sent (I know this is shown in
the email but this is a goverment office we send to so they make it as
difficult as possible I think. My sheet has 20 rows with a possibility of 20
dates and time in columns A and B but we will more than likely have no more
than 6. Any suggestions would help.

This can be done with a macro associated with the Workbook_Open() event. The
code would take the current date/time and place them into the first empty
cells in column A and B. It would not put "Now()" into the sheet, but the
actual value of Now() so that it would not change later.

The problem with this is possibly two-fold:
#1 - each time the workbook is opened, by anyone, the next cells in A/B
would get date/time entered into them.
#2 - the government side may not like you sending them a workbook with
macros in it. Although pre-coordination with them could deal with that. Of
course, if they leave macros disabled at their end, then no date/time would
get entered when they open the file.

One potential solution to #1 would be to check the username who has just
opened the workbook, and if it's not someone in your office, then don't enter
the date/time on the sheet.

I was going to say that you could examine the cell where the time/date were
to be entered and something was in them, then don't enter anything. But you
say there can be multiple entries (up to 20) - so it's difficult to deal with
that situation.

This isn't an impossible situation, but I think we need to know more about
how it's all working. Maybe I don't have a good picture of what sheets are
being sent, not sent, kept, not kept and which need date/time in which and
how many cells.

"tankerman" wrote:

Is there a way so that when my sheet is opened up the first time NOW will put
in the correct date in one cell and the time in another but when it is open
again the time wont change.

The sheet we use is sent to an individual and they inturn open it, copy and
paste rows to a master sheet they have (they wont use ACCESS which I think
would be easier). The sheet we send we do not keep so we have the sheet as an
XLT (2003 is our version) so someone doesn't accidently save and mess up our
master copy.

Column "A" is date sent, Column "B" is time sent (I know this is shown in
the email but this is a goverment office we send to so they make it as
difficult as possible I think. My sheet has 20 rows with a possibility of 20
dates and time in columns A and B but we will more than likely have no more
than 6. Any suggestions would help.

I have a VB module in this spreadsheet that sends it, the code was written by
Ron De Bruin and it works great, this code does not follow the sheet when it
is emailed, I checked by sending it to myself and no code is in the
spreadsheet that is sent which works great because the recipient does not
know you use any VB to send the email, what I was hoping was that there was
someway to do the NOW function and not have the code follow the sheet as
Ron's does. The local gov office goes spastic when you mention anything
about coding to them, they think all code is evil and only used to be
distructive so there is no discussing anything like coding with them SO I'll
just do it the old fashion way and enter the dates and times manually. Alot
of the guys here are one finger peckers so it just takes awhile to get it
done, sometimes manually is better. Thanks for your responce.

"JLatham" wrote:

This can be done with a macro associated with the Workbook_Open() event. The
code would take the current date/time and place them into the first empty
cells in column A and B. It would not put "Now()" into the sheet, but the
actual value of Now() so that it would not change later.

The problem with this is possibly two-fold:
#1 - each time the workbook is opened, by anyone, the next cells in A/B
would get date/time entered into them.
#2 - the government side may not like you sending them a workbook with
macros in it. Although pre-coordination with them could deal with that. Of
course, if they leave macros disabled at their end, then no date/time would
get entered when they open the file.

One potential solution to #1 would be to check the username who has just
opened the workbook, and if it's not someone in your office, then don't enter
the date/time on the sheet.

I was going to say that you could examine the cell where the time/date were
to be entered and something was in them, then don't enter anything. But you
say there can be multiple entries (up to 20) - so it's difficult to deal with
that situation.

This isn't an impossible situation, but I think we need to know more about
how it's all working. Maybe I don't have a good picture of what sheets are
being sent, not sent, kept, not kept and which need date/time in which and
how many cells.

"tankerman" wrote:

Is there a way so that when my sheet is opened up the first time NOW will put
in the correct date in one cell and the time in another but when it is open
again the time wont change.

The sheet we use is sent to an individual and they inturn open it, copy and
paste rows to a master sheet they have (they wont use ACCESS which I think
would be easier). The sheet we send we do not keep so we have the sheet as an
XLT (2003 is our version) so someone doesn't accidently save and mess up our
master copy.

Column "A" is date sent, Column "B" is time sent (I know this is shown in
the email but this is a goverment office we send to so they make it as
difficult as possible I think. My sheet has 20 rows with a possibility of 20
dates and time in columns A and B but we will more than likely have no more
than 6. Any suggestions would help.

I was just reading some questions and answers and just got mine answered

ctrl + ; puts the current date in for us and
ctrl + shift + ; puts the current time in for us

Never hurts to lurk and read


"tankerman" wrote:

I have a VB module in this spreadsheet that sends it, the code was written by
Ron De Bruin and it works great, this code does not follow the sheet when it
is emailed, I checked by sending it to myself and no code is in the
spreadsheet that is sent which works great because the recipient does not
know you use any VB to send the email, what I was hoping was that there was
someway to do the NOW function and not have the code follow the sheet as
Ron's does. The local gov office goes spastic when you mention anything
about coding to them, they think all code is evil and only used to be
distructive so there is no discussing anything like coding with them SO I'll
just do it the old fashion way and enter the dates and times manually. Alot
of the guys here are one finger peckers so it just takes awhile to get it
done, sometimes manually is better. Thanks for your responce.

"JLatham" wrote:

This can be done with a macro associated with the Workbook_Open() event. The
code would take the current date/time and place them into the first empty
cells in column A and B. It would not put "Now()" into the sheet, but the
actual value of Now() so that it would not change later.

The problem with this is possibly two-fold:
#1 - each time the workbook is opened, by anyone, the next cells in A/B
would get date/time entered into them.
#2 - the government side may not like you sending them a workbook with
macros in it. Although pre-coordination with them could deal with that. Of
course, if they leave macros disabled at their end, then no date/time would
get entered when they open the file.

One potential solution to #1 would be to check the username who has just
opened the workbook, and if it's not someone in your office, then don't enter
the date/time on the sheet.

I was going to say that you could examine the cell where the time/date were
to be entered and something was in them, then don't enter anything. But you
say there can be multiple entries (up to 20) - so it's difficult to deal with
that situation.

This isn't an impossible situation, but I think we need to know more about
how it's all working. Maybe I don't have a good picture of what sheets are
being sent, not sent, kept, not kept and which need date/time in which and
how many cells.

"tankerman" wrote:

Is there a way so that when my sheet is opened up the first time NOW will put
in the correct date in one cell and the time in another but when it is open
again the time wont change.

The sheet we use is sent to an individual and they inturn open it, copy and
paste rows to a master sheet they have (they wont use ACCESS which I think
would be easier). The sheet we send we do not keep so we have the sheet as an
XLT (2003 is our version) so someone doesn't accidently save and mess up our
master copy.

Column "A" is date sent, Column "B" is time sent (I know this is shown in
the email but this is a goverment office we send to so they make it as
difficult as possible I think. My sheet has 20 rows with a possibility of 20
dates and time in columns A and B but we will more than likely have no more
than 6. Any suggestions would help.

Ron does great work. I've got little tidbits of his laying around here myself.

Would it be possible for you to send me a workbook with the code you have
that does the job for you? I'm betting that just a couple of lines of code
added to it will automatically add the date/time to the sheet you send to the
Government without you having to lift a finger.

From the last posts you've put up I get the idea that you put the date/time
on the one sheet you send to them and are also recording that same date/time
on another sheet that you keep?

Anyhow - if you can send it, or even a close to reality representative
workbook with the code from Ron in it to (remove spaces) HelpFrom @
JLathamsite . com I'll certainly look at it and see if we can't make the
whole thing a one-button solution for you.

"tankerman" wrote:

I was just reading some questions and answers and just got mine answered

ctrl + ; puts the current date in for us and
ctrl + shift + ; puts the current time in for us

Never hurts to lurk and read


"tankerman" wrote:

I have a VB module in this spreadsheet that sends it, the code was written by
Ron De Bruin and it works great, this code does not follow the sheet when it
is emailed, I checked by sending it to myself and no code is in the
spreadsheet that is sent which works great because the recipient does not
know you use any VB to send the email, what I was hoping was that there was
someway to do the NOW function and not have the code follow the sheet as
Ron's does. The local gov office goes spastic when you mention anything
about coding to them, they think all code is evil and only used to be
distructive so there is no discussing anything like coding with them SO I'll
just do it the old fashion way and enter the dates and times manually. Alot
of the guys here are one finger peckers so it just takes awhile to get it
done, sometimes manually is better. Thanks for your responce.

"JLatham" wrote:

This can be done with a macro associated with the Workbook_Open() event. The
code would take the current date/time and place them into the first empty
cells in column A and B. It would not put "Now()" into the sheet, but the
actual value of Now() so that it would not change later.

The problem with this is possibly two-fold:
#1 - each time the workbook is opened, by anyone, the next cells in A/B
would get date/time entered into them.
#2 - the government side may not like you sending them a workbook with
macros in it. Although pre-coordination with them could deal with that. Of
course, if they leave macros disabled at their end, then no date/time would
get entered when they open the file.

One potential solution to #1 would be to check the username who has just
opened the workbook, and if it's not someone in your office, then don't enter
the date/time on the sheet.

I was going to say that you could examine the cell where the time/date were
to be entered and something was in them, then don't enter anything. But you
say there can be multiple entries (up to 20) - so it's difficult to deal with
that situation.

This isn't an impossible situation, but I think we need to know more about
how it's all working. Maybe I don't have a good picture of what sheets are
being sent, not sent, kept, not kept and which need date/time in which and
how many cells.

"tankerman" wrote:

Is there a way so that when my sheet is opened up the first time NOW will put
in the correct date in one cell and the time in another but when it is open
again the time wont change.

The sheet we use is sent to an individual and they inturn open it, copy and
paste rows to a master sheet they have (they wont use ACCESS which I think
would be easier). The sheet we send we do not keep so we have the sheet as an
XLT (2003 is our version) so someone doesn't accidently save and mess up our
master copy.

Column "A" is date sent, Column "B" is time sent (I know this is shown in
the email but this is a goverment office we send to so they make it as
difficult as possible I think. My sheet has 20 rows with a possibility of 20
dates and time in columns A and B but we will more than likely have no more
than 6. Any suggestions would help.