I have a worksheet I use with a small handful of colleagues on a daily basis to share information with about orders we place. To make sure we always have the same up to date info we use a shared excel workbook I created. I know this is not preferred, but this is all that is available in my company.
To prevent us from overwriting our existing data with new orders I created this code:
If ActiveWorkbook.MultiUserEditing Then 'in case of multiple users, make sure all data is accepted. ActiveWorkbook.AcceptAllChanges End If ActiveWorkbook.Save 'save to retrieve latest version from server. Sheets("Database").AutoFilterMode = False 'disengage autofilter so hidden lines won't be overwritten Dim I As String If Sheet2.Range("A:A").Find(What:=User(), LookIn:=xlValues) Is Nothing Then I = Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1 'determine next available blank row. Else I = Sheet2.Range("A:A").Find(What:=User(), LookIn:=xlValues).Row End If Range("Database!A" & I).Value = User() ActiveWorkbook.Save Dim MyLine(0 To 15) As Variant MyLine(0) = Application.WorksheetFunction.Max(Range("Database!A:A")) + 1 'determine next available line number and auto-fill on next blank row. If Range("Form!C6") = "" Then 'if multi-line box is empty, copy over material type box. MyLine(1) = Range("Form!B3").Value Else 'if multi-line box is filled, copy over multi-line box. MyLine(1) = Range("Form!C6").Value End If MyLine(2) = Range("Form!B4").Value 'copy over all info filled in the form to next blank row on database MyLine(3) = Range("Form!B5").Value MyLine(4) = Range("Form!B6").Value MyLine(5) = Range("Form!B7").Value MyLine(6) = Range("Form!B8").Value MyLine(7) = "LA" & Range("Form!B9").Value MyLine(8) = Range("Form!B10").Value MyLine(10) = Range("Form!B11").Value MyLine(9) = Range("Maintenance!D2").Value 'auto-fill phone number from filtered results on contacts sheet MyLine(14) = Environ$("UserName") 'Pull username from system MyLine(15) = Now 'pull current time from system MyLine(11) = Range("Form!B12").Value If Range("Form!B5") = "Stores" Then 'in case of stores orders auto fill PO box and set PO text as black MyLine(12) = "None" MyLine(13) = "None" Range("Database!N" & I).Font.ColorIndex = 0 End If Range("Database!A" & I & ":Database!P" & I).Value = MyLine ActiveWorkbook.SaveCopyAs ("C:\Redacted\" & Format(Now(), "DD-MM-YYYY hh mm ss") & ".xlsm") 'save backup with form still filled in, named for current date and time. Sheet1.Range("Form!B3:B14").ClearContents Sheet1.Range("Form!C3").ClearContents Sheet1.Range("Form!C6:C7").ClearContents Range("Database!A1").AutoFilter 'switch auto-filter for database back on ActiveWorkbook.Save 'save to database to ensure latest version is available. Beep 'Audio prompt saying macro is done. End Sub
My structure includes a fair number of saves to update the server file, as the code runs fairly slowly. If it is interrupted at any point, due to this structure no information will be overwritten. This is all to account for multiple users clicking this button at the same time on different machines, interrupting each others saves.
Currently I get around this by "Claiming" the next available line at the start of the run, and restarting on that line the next time the button is clicked (when someone else's save action is complete) however this is very heavy on Save actions, and due to it being a server located file, this slows down the macro significantly. Is there a better structure I can use to account for multiple users clicking the button at the same time, preventing any information being overwritten at any cost?
PS I know using Activeworkbook and the like is discouraged, and I'm currently reviewing my code to amend this. I'm just looking for ideas about above issue.