none
Help with auto sequence numbers RRS feed

  • Question

  • Hello - 

    I need to generate an auto sequence numbering system. I would like to have a particular cell "Sheets("1").Range("D10")" auto generate a new number each time the workbook is opened.

    Sequence can start at 1 and would need to keep generating a new number (2,3,4,5...) each time the workbook is opened.

    Anyone know how this can be done?

    Saturday, August 27, 2016 12:28 AM

Answers

  • Press Alt+F11 to activate the Visual Basic Editor.

    Double-click on ThisWorkbook under Microsoft Excel Objects.

    Copy the following code into the workbook module:

    Private Sub Workbook_Open()
        With Worksheets("1").Range("D10")
            .Value = .Value + 1
        End With
    End Sub

    The above code assumes that the sheet is really named 1 as in your post. If you meant the first worksheet, replace 1 with the name of the first sheet, or omit the quotes: Worksheets(1).

    Switch back to Excel and save the workbook in a format that supports macros: .xlsm, .xlsb or .xls, but not .xlsx.

    Make sure that you allow macros when you open the workbook, and that you save it when you close it.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, August 27, 2016 7:32 AM

All replies

  • Press Alt+F11 to activate the Visual Basic Editor.

    Double-click on ThisWorkbook under Microsoft Excel Objects.

    Copy the following code into the workbook module:

    Private Sub Workbook_Open()
        With Worksheets("1").Range("D10")
            .Value = .Value + 1
        End With
    End Sub

    The above code assumes that the sheet is really named 1 as in your post. If you meant the first worksheet, replace 1 with the name of the first sheet, or omit the quotes: Worksheets(1).

    Switch back to Excel and save the workbook in a format that supports macros: .xlsm, .xlsb or .xls, but not .xlsx.

    Make sure that you allow macros when you open the workbook, and that you save it when you close it.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, August 27, 2016 7:32 AM
  • Thank you Hans! As usual, you rock! Lol...you made it look so simple :)

    So another sort of related question...let me know if I should post a new one for this....so is there a way to deactivate the above code?

    I will be transmitting this file automatically, using a submit button. Would there be a way to disable this code when the submit button is clicked?

    Monday, August 29, 2016 10:08 PM
  • Sorry, I don't understand your question.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, August 29, 2016 11:18 PM