Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
autonumbering only when original file is opened
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
autonumbering only when original file is opened
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
autonumbering only when original file is opened
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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to tell number of spaces between values in saved text file fromthe original xls file | Excel Discussion (Misc queries) | |||
data missing in charts original file 97-2003 opened 2007 and then. | Charts and Charting in Excel | |||
auto save excel file every 10 minutes to its original file name | Excel Discussion (Misc queries) | |||
Auto save replaced my original file and now I need the original? | Excel Discussion (Misc queries) | |||
How set file open path to filepath of file opened with Explorer ? | Excel Programming |