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.
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.
        I = Sheet2.Range("A:A").Find(What:=User(), LookIn:=xlValues).Row
End If
Range("Database!A" & I).Value = User()
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.
   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.

  • \$\begingroup\$ Is there more code that isn't shown here where the comment '!!code adding the new info to the next available blank row runs here is? \$\endgroup\$
    – pacmaninbw
    Sep 23, 2019 at 13:51
  • \$\begingroup\$ Yes @pacmaninbw, however this isn't very relevant to this structure. It's a glorified copy paste from the first sheet where we enter our data to the second sheet where we keep our records. This is the action that cannot be interrupted at any cost hence this structure around it. Sorry if this isn't very clear. \$\endgroup\$
    – Plutian
    Sep 23, 2019 at 13:55
  • \$\begingroup\$ To be able to provide the best review possible, we need all the code at least in the function. Questions that obviously don't have all the code will be put on hold with the reason being Lack of Concrete Context. To put a question on hold requires 5 reviewers to vote to close the question. You might want to take a look at the help pages at codereview.stackexchange.com/help/how-to-ask and codereview.stackexchange.com/help/dont-ask \$\endgroup\$
    – pacmaninbw
    Sep 23, 2019 at 14:03
  • 2
    \$\begingroup\$ You might want to look at a database solution where all the data is stored in a database and each user has their own workbook with data that gets pulled from the database. MariaDB and MySQL are 2 free database systems. Don't use MS Access because it doesn't lock the database properly. \$\endgroup\$
    – pacmaninbw
    Sep 23, 2019 at 14:06
  • 1
    \$\begingroup\$ @pacmaninbw I understand. I cut out the code as it is not where the problem is, the data save action takes a few seconds at most, however the saves take significantly longer to complete. I have now amended my question. \$\endgroup\$
    – Plutian
    Sep 23, 2019 at 14:10


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.