Results 1 to 4 of 4

Thread: Excel VBA Macro for Automatic Sequential Invoice Number Generator upon opening that works for a shared document
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Excel VBA Macro for Automatic Sequential Invoice Number Generator upon opening that works for a shared document

    Hello, I am a beginner with macros and am having trouble. I have created a macro that creates a new sequential Invoice number upon opening a protected document. My issue is that several people in the company will be using this same document. How can I make sure that If several people have the document open, they each get a different Invoice number so that they are no duplicate invoice numbers.

    Here is the code I currently have that I mentioned above that works, but not if several people try to access the doc:

    Private Sub Workbook_Open()
    ActiveSheet.Unprotect
    With Range("K2")
    .NumberFormat = "10000"
    .Value = .Value + 1
    End With
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Sub


    Here is the rest of the macros that I created in the document as well, in case they are affecting it somehow:

    Sub SaveAsPDF()
    '
    ' SaveAsPDF Macro
    '


    '
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    "/Users/brittneywilliams/Desktop/Quote_" & Range("K2").Text, Quality:=xlQualityMinimum, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    False
    End Sub



    Sub ClearContents()
    '
    ' ClearContents Macro
    '


    '
    Range( _
    "E4,H4,J4:K4,C8:J8,C10:D10,F10:G10,I10:J10,C12:E12,I12:J12,C17:F17,J17,C19:J19,C21:F21,J21,C23:F23,I23:J23,C25:J25,C27:D2 7,G27:H27,C29:D29,C31:F31,C33:J33,C40,F40,I40,C42,F42,I42,C48,F48,I48,H50:J50,H52:J52,C72:J74" _
    ).Select
    Selection.ClearContents
    End Sub



    Sub ClearCheckBoxes()
    'Updateby Extendoffice 20161129
    Dim chkBox As Excel.CheckBox
    Application.ScreenUpdating = False
    For Each chkBox In ActiveSheet.CheckBoxes
    chkBox.Value = xlOff
    Next chkBox
    Application.ScreenUpdating = True
    End Sub



    Sub ResetValues()
    '
    ' ResetValues Macro
    '


    '
    Range("C38:E38").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("D40").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G40").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("J40").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("D42").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G42").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("J42").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("D48").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G48").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("J48").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("C72").Select
    End Sub



    Private Sub RunAllMacros()
    Call ClearContents
    Call ResetValues
    Call ClearCheckBoxes
    End Sub




    Any help would be much appreciated! Thank you!

  2. #2
    Board Regular baitmaster's Avatar
    Join Date
    Mar 2009
    Location
    bristol, england
    Posts
    1,950
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Macro for Automatic Sequential Invoice Number Generator upon opening that works for a shared document

    welcome to the board

    You could make this work as requested by having an external file that tracks the unique ID, which is then updated when anyone uses it. Say a spreadsheet called "tracker" in a fixed location, that is never touched by anyone and that has this value in sheet1 cell A1, where your code opens the file, update it and save automatically. It's a bit clumsy but would work OK. Working with a database-type file connecting via SQL connection would be much slicker but relies on IMO stronger Excel skills - but I suggest you look into it

    Alternatively, you could
    - append your users initials to the start of the reference. Get their initials from their login as follows, assuming your login is of the format name.surname - check the username value in case you need to adjust this code, by typing ?Environ("username") in the Immediate window
    Code:
    Dim str As String: str = Environ("username")
    Dim initials As String: initials = Left(str, 1) & Mid(str, InStr(str, ".") + 1, 1)
    - append a date/time stamp to every reference as this ensures uniqueness. The format YYYYMMDD is always in alphabetical order

    Your code as written makes a classic mistake of selecting everything. You don't need to do this, you can refer directly to any object, so your code becomes much simplified
    Code:
    Sub ResetValues()Dim rng As Range: Set rng = Range("C38:E38, D40, G40, J40, d42, g42, j42, d48, j48, c72")
    rng.FormulaR1C1 = "0"
    End Sub
    Last edited by baitmaster; Jun 19th, 2019 at 09:33 AM.
    Baitmaster G

    Excel 2013 on Win 7 (work), 2010 on Win 8 (home), 2007 on Win 7 (in the spare room)

    Always save your work before running code from me, I probably haven't tested it and I'm not responsible when you lose all your work :p
    I aim to improve understanding so you can develop your own solutions, not do all your work for you. Write a clear requirement and you're more likely to get help

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Macro for Automatic Sequential Invoice Number Generator upon opening that works for a shared document

    Thank you! I will give these options a try and get back to you if I run in to any issues. And thank you for the ResetValues tip as well. I tried something similar but it wouldn't run unless I selected each cell individually. The code you provided is slightly different though, so I will try that as well. Thanks again.

  4. #4
    Board Regular baitmaster's Avatar
    Join Date
    Mar 2009
    Location
    bristol, england
    Posts
    1,950
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Macro for Automatic Sequential Invoice Number Generator upon opening that works for a shared document

    No problem

    A quick tip for you. VBA is using the syntax Object.Method or Object.Property, where Object is the thing you're working with, Method is what you're doing to the thing, and Property is information about the thing. When you record a macro it follows this rule, so Range("").Select is selecting the range, in order to create the Selection object. Then Selection.Whatever is doing whatever to the selection object - but you don't need to create the Selection object, you already have an object to work with! Range("").Whatever does exactly the same without selecting anything

    So:
    - don't ever select anything in your code, it's ugly and unstable
    - fully understand how Objects are created and used, so you always refer to the real thing you want to work with. Read up on VBA / Excel object model - Objects can include workbooks, worksheets, ranges, names, shapes, etc..
    Baitmaster G

    Excel 2013 on Win 7 (work), 2010 on Win 8 (home), 2007 on Win 7 (in the spare room)

    Always save your work before running code from me, I probably haven't tested it and I'm not responsible when you lose all your work :p
    I aim to improve understanding so you can develop your own solutions, not do all your work for you. Write a clear requirement and you're more likely to get help

Some videos you may like

User Tag List

Tags for this Thread

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
  •