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

[csimarketing]

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:D27,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!

 

[baitmaster]

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

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

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

 

[csimarketing]

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.

 

[baitmaster]

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