autonumbering only when original file is opened

[Candee]

Good morning all.

I have created a form for users to fill in on a worksheet in a fil
named "IPR Test". It is designed so that the initial user will fil
out the first portion of cells, click a button at the bottom that wil
open another file (Log) and copy the data, save and close the log file
save the file, and send it via email. In cell V5, it has a formula t
add the next number (autonumber), and the file saves with this code:

FName = "\\Nfil108\qa\Shared Files\Testing\" & "IPR "
Range("A1").Value & ".xls"
ThisWorkbook.SaveCopyAs FileName:=FName

Everything works as it should with one little glitch. I need to b
able to turn the autonumbering formula off once the file has been save
with the new name. As it is now, when the e-mail recipients open th
file from their e-mail, it bumps up to the next number, when I want i
to stay the same. In other words, the only time I want the number t
increase is when the original file "IPR Test" is opened.

Any help would be greatly appreciated



Thanks in advanc

 

[pwdiaz]

This may not be the most elegant solution but I had a similar proble
where an instrument needed to open the same spreadsheet twice but onl
needed to diplay a userform the first time the spreadsheet was opened.
I solved this problem by using a method that I called "toggle".
Basically, the idea is to have a value placed into a cell as a resul
of an "event". In this example the value in sheet2 cell A1 is toggle
between 0 and 1 depending on the BeforeSave and BeforeClose events.
You might envison then that your autonumbering procedure is execute
depending upon wheather the value in Sheet2, cell A1 is a 0 or 1. Th
way this code works is that when the workbook is opened the value i
cell A1 is "0" and therefore the autonumbering procedure can b
activated (I didn't write in the autonumbering code just the toggl
procedure). When the workbook is saved with the saveas function th
value in the new workbook is changed to "1" while the value in th
original workbook remains as "0". When the new workbook is opened b
the autonumbering procedure remains silent because the toggle value i
"1". Other programmers probably have a more elegant solution bu
here's what works for me(sheet2 can be hidden so no one inadvertantl
changes the toggle value):

Private Sub workbook_beforesave(ByVal savasUI _
As Boolean, cancel As Boolean)

Sheets("sheet2").Activate
Range("a1").Activate
ActiveCell.Value = toggle
If toggle = 0 Then

Sheets("Sheet2").Activate
Range("a1").Activate
ActiveCell.Value = 1

End If

End Sub

Private Sub workbook_beforeclose(cancel As Boolean)

Sheets("sheet2").Activate
Range("a1").Activate
ActiveCell.Value = toggle
If toggle = 1 Then
Exit Sub

Sheets("sheet2").Activate
Range("a1").Activate
ActiveCell.Value = 0

End If

End Su

 

[Candee]

Thank you for your response, pwdiaz, and sorry for taking so long t
reply.

If your solution will solve the problem I'm having, it doesn't matte
if it is not the most elegant, as long as it works. I'm going to g
try it now.

Thanks again, and have a great day everyone!!!:cool