I have a template with commands in Open event that enter today's date in 1
cell and a sequential invoice number (from a text file) in another cell. I
need a way to have just the values in these cells once the workbook is saved,
sort of like Paste Special does. Otherwise, when the regular *xls file is
opened the next time it updates the date and uses the next sequential number.
I really need the worksheet to be static with the date it was created and
invoice number it was given instead of updating through the Open event.

I can't use the Paste special because there aren't formuals in the cells. I
can't turn off all macros in the saved *.xls file because there are some user
interface pieces that still need to work.

I thought of copying entire sheet to a new workbook to be saved. The
problem there is a 2nd worksheet in the workbook contains links (some using
macros) to the invoice worksheet- things such as shipping charges, taxes,
discounts, etc. If the user saves the invoice but still then needs to make a
correction, the links wouldn't work if I turn off all macros.

Any ideas?
Thanks,
Marcia

The simplest way: place a tag in a cell at the end of your open event. For
example place an "x" in D25. Have you open event check for the "x" at the
beginning, before entering the date or invoice number.

Now the event will only update the cells the first time it fires.

"Office User" wrote:

I have a template with commands in Open event that enter today's date in 1
cell and a sequential invoice number (from a text file) in another cell. I
need a way to have just the values in these cells once the workbook is saved,
sort of like Paste Special does. Otherwise, when the regular *xls file is
opened the next time it updates the date and uses the next sequential number.
I really need the worksheet to be static with the date it was created and
invoice number it was given instead of updating through the Open event.

I can't use the Paste special because there aren't formuals in the cells. I
can't turn off all macros in the saved *.xls file because there are some user
interface pieces that still need to work.

I thought of copying entire sheet to a new workbook to be saved. The
problem there is a 2nd worksheet in the workbook contains links (some using
macros) to the invoice worksheet- things such as shipping charges, taxes,
discounts, etc. If the user saves the invoice but still then needs to make a
correction, the links wouldn't work if I turn off all macros.

Any ideas?
Thanks,
Marcia

Cool idea, I'll give it a go. :-)
Marcia



"Damon Longworth" wrote:

The simplest way: place a tag in a cell at the end of your open event. For
example place an "x" in D25. Have you open event check for the "x" at the
beginning, before entering the date or invoice number.

Now the event will only update the cells the first time it fires.

"Office User" wrote:

I have a template with commands in Open event that enter today's date in 1
cell and a sequential invoice number (from a text file) in another cell. I
need a way to have just the values in these cells once the workbook is saved,
sort of like Paste Special does. Otherwise, when the regular *xls file is
opened the next time it updates the date and uses the next sequential number.
I really need the worksheet to be static with the date it was created and
invoice number it was given instead of updating through the Open event.

I can't use the Paste special because there aren't formuals in the cells. I
can't turn off all macros in the saved *.xls file because there are some user
interface pieces that still need to work.

I thought of copying entire sheet to a new workbook to be saved. The
problem there is a 2nd worksheet in the workbook contains links (some using
macros) to the invoice worksheet- things such as shipping charges, taxes,
discounts, etc. If the user saves the invoice but still then needs to make a
correction, the links wouldn't work if I turn off all macros.

Any ideas?
Thanks,
Marcia

the paste special should work,once you have pasted in the now() date just
copy and paste special as a value on top of itself,i do this a lot...

--
paul
remove nospam for email addy!



"Office User" wrote:

Cool idea, I'll give it a go. :-)
Marcia



"Damon Longworth" wrote:

The simplest way: place a tag in a cell at the end of your open event. For
example place an "x" in D25. Have you open event check for the "x" at the
beginning, before entering the date or invoice number.

Now the event will only update the cells the first time it fires.

"Office User" wrote:

I have a template with commands in Open event that enter today's date in 1
cell and a sequential invoice number (from a text file) in another cell. I
need a way to have just the values in these cells once the workbook is saved,
sort of like Paste Special does. Otherwise, when the regular *xls file is
opened the next time it updates the date and uses the next sequential number.
I really need the worksheet to be static with the date it was created and
invoice number it was given instead of updating through the Open event.

I can't use the Paste special because there aren't formuals in the cells. I
can't turn off all macros in the saved *.xls file because there are some user
interface pieces that still need to work.

I thought of copying entire sheet to a new workbook to be saved. The
problem there is a 2nd worksheet in the workbook contains links (some using
macros) to the invoice worksheet- things such as shipping charges, taxes,
discounts, etc. If the user saves the invoice but still then needs to make a
correction, the links wouldn't work if I turn off all macros.

Any ideas?
Thanks,
Marcia