Automatic numbering

[jesse]

how do i get an excel spreadsheet to automatically number
itself by 1 in a cell everytime a user opens it?

any ideas? I want to start the sprdsheet by 0001, and
then another user opens it and its now marked, 0002.

 

[Frank Kabel]

Hi
ut the following code in your workbook module (not in a standard
module):
Private Sub Workbook_Open()
with Worksheets("Sheet1")
.Range("A1").Value = Range("A1").value + 1
end with
End Sub

 

[Guest]

I haven't got a clue how to do this?

Can you help and provide me with the basic steps?

Thanks,

Jesse

 

[Anne Troy]

Open your workbook.
Hit Alt+F11 to take you to the Visual Basic Editor (VBE).
On the left, double-click ThisWorkbook under your file's name. Copy and
paste Frank's code into the code window at right. Hit the diskette on the
toolbar to save the code into the file. Close the VBE with the X at top
right.
This is a workbook_open event. That means it won't take effect until you
open the workbook with the code in it. That means you need to close it now,
and reopen it, to see the code work.

<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*->
Hope this helps!
Anne Troy (better known as Dreamboat)
Author: Dreamboat on Word
Email: Dreamboat*at*Piersontech.com
Web: www.TheOfficeExperts.com

 

[Frank Kabel]

Hi
just put this in your workbook module:
- open the workbook
- hit ALT-F11
- locate 'ThisWorkbook' in the left project explorer window
- double click on 'ThisWorkbook'
- paste the code in the appearing editor window
- close the VBA editor and save the workbook

For more about event procedures have a look at
http://www.cpearson.com/excel/events.htm

 

[JE McGimpsey]

One way:

Put this in the workbook's ThisWorkbook code module:

Private Sub Workbook_Open
With Me.Sheets("Sheet1").Range("A1")
.Value = .Value + 1
.Numberformat = "0000"
End With
End Sub

 

[Jesse]

Thanks, this worked, however, I'm trying to put this into
a different cell (G3) and when I changed the cell in the
code from A1 to G3, it errors.

Any ideas?

 

[jesse]

how do you get this value to move from cell A1 to cell
G3?? I tried changing the code and it doesn't work.

 

[JE McGimpsey]

What do you mean by "doesn't work"? Do you get a compile error? A VBA
run-time error? The wrong value? No value? A crash?

This is the only change your code should need:

With Me.Sheets("Sheet1").Range("G3")