Sequential Numbering in Excel 2003

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

sonicsi

Thread Starter
Joined
Dec 28, 2010
Messages
3
Hi folks,
I have become a subscriber of this forum because most of you that will be reading this will know far more about what I will be asking about so Hello all thanks for listening to my waffle and hear is your starter for ten.

I am trying to make a number in a single cell in a form generated from an Excel work book page, increase by 1 every time that the form is saved and printed, as a serial number.

e.g. – when the form is printed it is automatically saved in a register and the number is incremented by 1, the cell is L2 on the attached file.

I am using Excel 2003 and Windows XP Home.

So folks dose that sound feasible?

Simon.
 

Attachments

Joined
Jul 1, 2005
Messages
8,546
Welcome to the board (and specifically, greetings from somewhere else in't UK :))

Yeah, it's totally feasible. You could add a button to run a very small macro which saves, prints, and racks up L2 by 1.

But you need to think carefully about the overall process(es). I mean, in the attached there's a very small macro:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Sheets("Sheet1").Range("M1") = Sheets("Sheet1").Range("M1") + 1
End Sub

, which (should be self-explanatory) racks up M1 with each print attempt. But ...

... what happens with "I pressed 'Print' by mistake :eek:"?

HTH :)
 

Attachments

sonicsi

Thread Starter
Joined
Dec 28, 2010
Messages
3
Thanks Bomb #21 for such a quick reply, but I'm a bit confused as on my form the cell M1 doesn’t exist and how is it saved to a register?
 
Joined
Jul 1, 2005
Messages
8,546
Sorry, I missed the significance of "saved in a register".

In the attached I've added 2 really basic sheets ("Form" and "Register") as a demo.

On "Form", complete B2 and B3, then click "Register". B1 increments, B2:B3 are cleared, and the details were logged on "Register". That should get you started. :)

Sub register()
If ActiveSheet.Name <> "Form" Then Exit Sub
If Range("B1") = "" Then Exit Sub

res = Application.Match(Range("B1"), Sheets("Register").Columns(1), 0)
If IsError(res) Then
LastReg = Sheets("Register").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Register").Cells(LastReg, 1) = Range("B1")
Sheets("Register").Cells(LastReg, 2) = Range("B2")
Sheets("Register").Cells(LastReg, 3) = Range("B3")
Range("B1") = Range("B1") + 1
Range("B2:B3").ClearContents
Else
MsgBox "Register number already used!"
End If
End Sub
 

Attachments

sonicsi

Thread Starter
Joined
Dec 28, 2010
Messages
3
Once again thanks for the info, I've now got to get my head round what you have told me so I may be a while.

Big thanks and a HAPPY NEW YEAR
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top