1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Sequential Numbering in Excel 2003

Discussion in 'Business Applications' started by sonicsi, Dec 31, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. sonicsi

    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.
     

    Attached Files:

  2. bomb #21

    bomb #21

    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 :)
     

    Attached Files:

  3. sonicsi

    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?
     
  4. bomb #21

    bomb #21

    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
     

    Attached Files:

  5. sonicsi

    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
     
  6. Sponsor

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 733,556 other people just like you!

Loading...
Similar Threads - Sequential Numbering Excel
  1. byoung7998
    Replies:
    1
    Views:
    397
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/971785

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice