How to create an automatic counter

I

I CAN FLY

Hi
I have created an excel file to log supplier concern, each concern need
a unique reference number

Example : 322
Each time i save the file or open the file, i'm saving the associated
information, and next time i open again the file, i need the number to
become : 323 automatically, so i do not dupplicate the info.

Anybody can help? :confused:
 
M

Marco

Hi

What you can do is add the following to your code

Sub AutoNew(

Order = System.PrivateProfileString("C:\Settings.Txt", "Macrosettings", "Order"
If Order = "" The
Order =
Els
Order = Order +
End I

System.PrivateProfileString("C:\Settings.Txt", "Macrosettings", "Order") = Orde
ActiveDocument.Bookmarks("Order").Range.InsertBefore Format(Order, "00#"
End Su

The first time te file settings.txt will be created. And every time you create a new file, the value = value + 1. Place the field in your document and you have your counter..

Good luck
Marco.
 
D

DNF Karran

2 places to store the info: A cell in a (hidden?) worksheet or in th
Names.

Range method:

Sub WorkbookOpen
thisworkbook.sheets(1).range("a1").value
thisworkbook.sheets(1).range("a1").value+1
End Sub

Names method

Sub WorkbookOpen
ThisWorkbook.Names(1).Name = "_"
(FormatNumber(Right(ThisWorkbook.Names(1).Name
Len(ThisWorkbook.Names(1).Name) - 1)) + 1)
End Sub

Dunca
 
N

Nigel

Hi Marco
I am interested in what you propose .....

Where is the object System.PrivateProfileString ?

The code does not work for me.

Cheers
Nigel

Marco said:
Hi,

What you can do is add the following to your code:

Sub AutoNew()

Order = System.PrivateProfileString("C:\Settings.Txt", "Macrosettings", "Order")
If Order = "" Then
Order = 1
Else
Order = Order + 1
End If

System.PrivateProfileString("C:\Settings.Txt", "Macrosettings", "Order") = Order
ActiveDocument.Bookmarks("Order").Range.InsertBefore Format(Order, "00#")
End Sub


The first time te file settings.txt will be created. And every time you
Click to expand...
create a new file, the value = value + 1. Place the field in your document
and you have your counter...
 
M

Marco

Hi Nigel

Just create a sub with the code, and in your macro use

Call AutoNe

There is no object needed. It's just a system command

Regards
Marco.
 
Top