excel 2003 auto-increment number in cell when opening

[glnhwilly]

I'm creating a Purchase Order form that increments the numeric value in a cell by 1 each time the workbook is opened. The user immediately 'saves as' using this numbr as the filename. I did this using a MicrosoftExcelObject 'ThisWorkbook' with the following code:

Private Sub Workbook_Open()
Sheets("PO").Range("G4").Value = Sheets("PO").Range("G4").Value + 1
ThisWorkbook.Save
End Sub

This works well but unfortunately the number increments each time the workbook is opened for viewing. Is there a way to delete the above code using a macro after the initial opening of the file, allowing multiple openings without incrementing the number?

Thank you.

 

[xlhelp]

Look for McGimpsey on a search engine along with keywords for your question.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[SkipVought]

Well what is the LOGIC for determining the difference?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Seems to me that the procedural logic for this kind of application might be, to only incriment the number when an overt deliberate event occurs to create a new invoice, like the user hitting a button control object.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

Yes, the issue is what does "initial" mean?

"the initial opening of the file"

HOW are you defining initial? HOW does the logic work that determines if THIS opening is "initial" and THAT opening is not.

I am Excel ignorant (freely admitted), but is there the equivalent to Word's DOCVARIABLE? If so, then this would be easy.

You have a document variable, presumably given a value at the start (even if it is ""). On the first opening, the variable is changed to something ("This has happened"?). On subsequent openings, the Open event checks the variable.

If it is "This has happened", then fugeddaboutit.

The variable could be Boolean. If False, do whatever it is for the "initial" opening, and set the Boolean to True.

If True (i.e. is has happened already), then....do nothing.

Gerry

 

[Gavona]

The user immediately 'saves as' using this numbr as the filename

So you don't want the value to increment if it is equal to the filename? Add the blue line to your code

Private Sub Workbook_Open()
[blue]If ThisWorkbook.Name = Sheets("PO").Range("G4").Value & ".xls" Then Exit Sub[/blue]
Sheets("PO").Range("G4").Value = Sheets("PO").Range("G4").Value + 1
ThisWorkbook.Save
[red]Application.GetSaveAsFilename (Sheets("PO").Range("G4").Value)[/red]
End Sub

To try to make sure that the file is indeed saved you could add the red line above.

Gavin

 

[Gavona]

Wouldn't it be better to increment the order number whenever the master workbook/template is saved rather than when it is opened? In other words use the BeforeSave event.....

And do explore Xlhelp's suggestion.

And of course these posts are all about VBA so we should be in forum707.

Gavin