Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Startup Macro (EXCEL 97 Win 98)

    I am very, very new to EXCEL VBA, but what I need to do is simple and after several hours of tinkering, I've decided to admit my limitations and seek some guidance. I need a macro that when a worksheet is opened an item number is advanced by one. I will using and alpha-numeric item number / identifier, like CD-0001. If CD-0001 is the initial seed value, the next time the file is opened I would like the value to be CD-0002, the third time CD-0003, etc. The "trigger" event to increment the item number by one is the opening of the "master" excel file. I also would like to save the completed excel file as cd-0001, cd-0002, etc after the user has finalized input. So after I figure out how to automatically increment the item number by one upon each opening of the "master" excel file, I'll be discerning how to create a macro button that will save the file using the current item number as the file name. Thank you in advance for your assistance and, more importantly, patience.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Startup Macro (EXCEL 97 Win 98)

    If the item number is in cell A1 on sheet Sheet1, then the code below, placed in the Workbook Open Event routine should do what you want:

    <pre>Private Sub Workbook_Open()
    With Worksheets("Sheet1").Range("A1")
    If .Value = "" Then
    .Value = "CD-0001"
    Else
    .Value = Left(.Value, 3) & Format(Right(.Value, 4) + 1, "0000")
    End If
    End With
    End Sub
    </pre>


    To place this code in the workbook open event routine, first open the VBE editor by pressing Atl+F11. Then if the Project Explorer window is not open, press Ctrl+R to open it. In the project explorer window, right click on the ThisWorkbook object and select "View Code" from the pop up menu. In the editor window, drop down the left drop down list and select "Workbook" from the list. Drop down the right drop down list and click on "Open". Now, replace the empty Workbook_Open routine with the one above.
    Legare Coleman

Posting Permissions

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