Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Adding an auto number

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    London, Tottenham
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    PLEASE HELP

    I am doing a project for college and need some help. I have a sheet that will open on a template but need an auto number to appear in two cells (E3:E4) automatically that are unique each time, preferably sequential. Can some1 help me??????????????

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    a little vba could fix this, but to verify the request, please confirm my understanding,

    each time a sheet is added to the workbook, two new numbers are generated sequentially following the highest value of any value in these two cells from any other sheet???

    try this in the workbook module...


    Private Sub Workbook_NewSheet(ByVal Sh As Object)
    maxval = 0
    For sn = 1 To ActiveWorkbook.Sheets.Count
    If Sheets(sn).Name <> Sh.Name Then
    If IsNumeric(Sheets(sn).Range("e3")) And Sheets(sn).Range("e3") > maxval Then maxval = Sheets(sn).Range("e3").Value
    If IsNumeric(Sheets(sn).Range("e4")) And Sheets(sn).Range("e4") > maxval Then maxval = Sheets(sn).Range("e4").Value
    End If
    Next
    Sh.Range("e3") = maxval + 1
    Sh.Range("e4") = maxval + 2
    End Sub



    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    London, Tottenham
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for your help. I kind of get it but still am a bit confused. Do i replace Sheets with the name of my sheet. Sorry to inconvenience u but is it possible for you to write it again as before but include the sheet name "invoice" in the appropriate places?? THANKS. I AM VERY GRATEFUL!!!

    FELLOW LONDONER!

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    no, i intended the code for use as-is. it searchs all sheets when you open a new one, and gets two new values sequentially higher than those that appear in the same cells on any other sheet that's already existing in the workbook.

    add it to the workbook module of a new book, then keep adding new sheets, you'll see what i mean...

    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-13 15:29, daleyman wrote:
    a little vba could fix this, but to verify the request, please confirm my understanding,

    each time a sheet is added to the workbook, two new numbers are generated sequentially following the highest value of any value in these two cells from any other sheet???

    try this in the workbook module...


    Private Sub Workbook_NewSheet(ByVal Sh As Object)
    maxval = 0
    For sn = 1 To ActiveWorkbook.Sheets.Count
    If Sheets(sn).Name <> Sh.Name Then
    If IsNumeric(Sheets(sn).Range("e3")) And Sheets(sn).Range("e3") > maxval Then maxval = Sheets(sn).Range("e3").Value
    If IsNumeric(Sheets(sn).Range("e4")) And Sheets(sn).Range("e4") > maxval Then maxval = Sheets(sn).Range("e4").Value
    End If
    Next
    Sh.Range("e3") = maxval + 1
    Sh.Range("e4") = maxval + 2
    End Sub


    Kind Regards,
    Ivan F Moala From the City of Sails

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    download the file no 14

    http://www.pexcel.com/download.htm

    here i have given the example of autonumbering.. or you can say invoicnumbering.

    see if it is helpful to you.

    file shows how you can use roman nos ..

    prefix and suffix numbering ..

    here you can even change start nos

    it is better if you can see this file.

    ni****h desai
    http://www.pexcel.com

  7. #7
    New Member
    Join Date
    Apr 2002
    Location
    London, Tottenham
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks everyone for your help. It is much appreciated!

    Love
    Naz

  8. #8
    New Member
    Join Date
    Apr 2002
    Location
    London, Tottenham
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    so far I have tried the following:

    On 2002-04-14 07:40, daleyman wrote:
    Private Sub Workbook_NewSheet(ByVal Sh As Object)
    maxval = 0
    For sn = 1 To ActiveWorkbook.Sheets.Count
    If Sheets(sn).Name <> Sh.Name Then
    If IsNumeric(Sheets(sn).Range("e3")) And Sheets(sn).Range("e3") > maxval Then maxval = Sheets(sn).Range("e3").Value
    If IsNumeric(Sheets(sn).Range("e4")) And Sheets(sn).Range("e4") > maxval Then maxval = Sheets(sn).Range("e4").Value
    End If
    Next
    Sh.Range("e3") = maxval + 1
    Sh.Range("e4") = maxval + 2
    End Sub


    ...is all you need, take away the extra "Sub AddNewNumber"...the statement:

    Private Sub Workbook_NewSheet(ByVal Sh As Object)

    ...opens the procedure, you need to paste the block of code into the module that relates to the Workbook. NewSheet is the event, therefore, whenever you add a new sheet this macro will run automatically, because excel will look in the Workbook module to see if there is such a procedure.

    If you don't know where the Workbook module is, go into the Visual Basic Editor and double click on the "ThisWorkbook" label, this will open up the code sheet for that object.

    Good Luck....
    _________________

    DALEY :P
    And this only worked when a new sheet was inserted and not for when a new sheet is opened from a template. So i tried the following:

    On 2002-04-14 08:15, daleyman wrote:
    try putting this code into the "ThisWorkbook" module of the worksheet...


    Private Sub Workbook_Open()

    val1 = Sheets("Sheet2").Range("E3").Value
    val2 = Sheets("Sheet2").Range("E4").Value

    If IsNumeric(val1) And IsNumeric(val2) Then
    If val1 > val2 Then val3 = val1 Else val3 = val2
    Sheets("sheet2").Range("E3").Value = val3 + 1
    Sheets("sheet2").Range("E4").Value = val3 + 2
    End If

    End Sub


    ...the idea being that every time you open the workbook, it gets the values from cells E3 and E4, and if they are numeric, substitutes them for two new values sequentially higher than the maximum of the two existing values...

    _________________

    DALEY :P
    but this kept giving the same values in e3 and e4 every time i opened a new worksheet from the template.

    What should happen is that every time a new sheet is opened from the template a different and unique number should appear in e3 and e4. Any more ideas???

    (thanks to Ni****h desai! but it only adds a new number going down the column each time!)

    Thanks

    Naz

    [ This Message was edited by: naq_uk on 2002-04-15 14:41 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •