Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have P.O. increment by 1 only on Save As
Could someone help me with a problem that a co-worker is having. What they
would like to do is have a P.O.increment to the next number. I thought I found the answer. After creating a template and pasting inthe following code. It appears to work. When I open the template the number is assigned as I want. But when I save an XLS spreadsheet, all looks good. That is until I open the spreadsheet and the number increases by one in the P.O. number field. The same thing happens when I open the template. Below is the code I'm using from mcgimpsey. What I would like to be able to do is have the number assigned to the next highest number, but remain static if I open the XLS spreadsheet and remain static in the Template until I do a save as to an XLS spreadsheet. And not automatically sequence to the previous number plus one upon opening the template.Any help would be appreciated.I've googled the posts for this subject and copied the following code from mcgimpsey. http://www.mcgimpsey.com/excel/udfs/...ums.htmlPublic Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long Const sDEFAULT_PATH As String = "<your path here" Const sDEFAULT_FNAME As String = "defaultseq.txt" Dim nFileNumber As Long nFileNumber = FreeFile If sFileName = "" Then sFileName = sDEFAULT_FNAME If InStr(sFileName, Application.PathSeparator) = 0 Then _ sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName If nSeqNumber = -1& Then If Dir(sFileName) < "" Then Open sFileName For Input As nFileNumber Input #nFileNumber, nSeqNumber nSeqNumber = nSeqNumber + 1& Close nFileNumber Else nSeqNumber = 1& End If End If On Error GoTo PathError Open sFileName For Output As nFileNumber On Error GoTo 0 Print #nFileNumber, nSeqNumber Close nFileNumber NextSeqNumber = nSeqNumber Exit Function PathError: NextSeqNumber = -1& End Function Public Sub Workbook_Open() ThisWorkbook.Sheets(1).Range("B2").Value = NextSeqNumber End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have P.O. increment by 1 only on Save As
In the thisworkbook code module add this event code, instead of using your
current open event... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) if SaveAsUI = true then 'increment the PO number... end if End Sub Not there is a SaveAsUI which you can use to catch if it is a save as and then increment the PO number... -- HTH... Jim Thomlinson "Joel" wrote: Could someone help me with a problem that a co-worker is having. What they would like to do is have a P.O.increment to the next number. I thought I found the answer. After creating a template and pasting inthe following code. It appears to work. When I open the template the number is assigned as I want. But when I save an XLS spreadsheet, all looks good. That is until I open the spreadsheet and the number increases by one in the P.O. number field. The same thing happens when I open the template. Below is the code I'm using from mcgimpsey. What I would like to be able to do is have the number assigned to the next highest number, but remain static if I open the XLS spreadsheet and remain static in the Template until I do a save as to an XLS spreadsheet. And not automatically sequence to the previous number plus one upon opening the template.Any help would be appreciated.I've googled the posts for this subject and copied the following code from mcgimpsey. http://www.mcgimpsey.com/excel/udfs/...ums.htmlPublic Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long Const sDEFAULT_PATH As String = "<your path here" Const sDEFAULT_FNAME As String = "defaultseq.txt" Dim nFileNumber As Long nFileNumber = FreeFile If sFileName = "" Then sFileName = sDEFAULT_FNAME If InStr(sFileName, Application.PathSeparator) = 0 Then _ sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName If nSeqNumber = -1& Then If Dir(sFileName) < "" Then Open sFileName For Input As nFileNumber Input #nFileNumber, nSeqNumber nSeqNumber = nSeqNumber + 1& Close nFileNumber Else nSeqNumber = 1& End If End If On Error GoTo PathError Open sFileName For Output As nFileNumber On Error GoTo 0 Print #nFileNumber, nSeqNumber Close nFileNumber NextSeqNumber = nSeqNumber Exit Function PathError: NextSeqNumber = -1& End Function Public Sub Workbook_Open() ThisWorkbook.Sheets(1).Range("B2").Value = NextSeqNumber End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have P.O. increment by 1 only on Save As
Thanks for your help. This does exactly what I want. It only changes on
Save As. Joel "Jim Thomlinson" wrote in message ... In the thisworkbook code module add this event code, instead of using your current open event... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) if SaveAsUI = true then 'increment the PO number... end if End Sub Not there is a SaveAsUI which you can use to catch if it is a save as and then increment the PO number... -- HTH... Jim Thomlinson "Joel" wrote: Could someone help me with a problem that a co-worker is having. What they would like to do is have a P.O.increment to the next number. I thought I found the answer. After creating a template and pasting inthe following code. It appears to work. When I open the template the number is assigned as I want. But when I save an XLS spreadsheet, all looks good. That is until I open the spreadsheet and the number increases by one in the P.O. number field. The same thing happens when I open the template. Below is the code I'm using from mcgimpsey. What I would like to be able to do is have the number assigned to the next highest number, but remain static if I open the XLS spreadsheet and remain static in the Template until I do a save as to an XLS spreadsheet. And not automatically sequence to the previous number plus one upon opening the template.Any help would be appreciated.I've googled the posts for this subject and copied the following code from mcgimpsey. http://www.mcgimpsey.com/excel/udfs/...ums.htmlPublic Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long Const sDEFAULT_PATH As String = "<your path here" Const sDEFAULT_FNAME As String = "defaultseq.txt" Dim nFileNumber As Long nFileNumber = FreeFile If sFileName = "" Then sFileName = sDEFAULT_FNAME If InStr(sFileName, Application.PathSeparator) = 0 Then _ sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName If nSeqNumber = -1& Then If Dir(sFileName) < "" Then Open sFileName For Input As nFileNumber Input #nFileNumber, nSeqNumber nSeqNumber = nSeqNumber + 1& Close nFileNumber Else nSeqNumber = 1& End If End If On Error GoTo PathError Open sFileName For Output As nFileNumber On Error GoTo 0 Print #nFileNumber, nSeqNumber Close nFileNumber NextSeqNumber = nSeqNumber Exit Function PathError: NextSeqNumber = -1& End Function Public Sub Workbook_Open() ThisWorkbook.Sheets(1).Range("B2").Value = NextSeqNumber End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Increment | Excel Worksheet Functions | |||
How to diasble save and save as menu but allow a save button | Excel Programming | |||
Increment/Increment letter in alphabetical order | Excel Programming | |||
Totally Disabling (^ save ) (Save as) and Save Icon – Which code do I use: | Excel Programming | |||
need to increment value | Excel Discussion (Misc queries) |