VBA Variable Being Overwritten When Collaborating

[chbrandt]

I've created an excel sheet to use for collaboration with team members where many people would be editing it at the same time via Teams. I have several duplicates of the code snippet below, one for each column that has a question, and people can click a + button to run the macro that uses InputBox to ask the question. Their answers are then automatically placed in the first blank cell below the question itself, skipping a row for aesthetics. The problem is, when two people click the + button and have the prompt open at the same time, the second person, and third, fourth and fifth person's answers will all be overwritten when the first person who clicked it finally hits enter and submits their response. Is there an easy way I'm not thinking of that would keep this from happening? It's also difficult to test, as it takes more than one person. Appreciate any ideas, as I'm totally stumped on this one.

Sub Q1AddQuestion()
'
' Q1AddQuestion Macro

    Dim Q1 As Variant
    Q1 = InputBox(Range("B1").Value)
    
    If IsEmpty(Range("B3")) Then
    
    Cells(Rows.Count, 2).End(xlUp).Select
    ActiveCell.Offset(1).Select
    ActiveCell.Value = Q1
    
    Else

    Cells(Rows.Count, 2).End(xlUp).Select
    ActiveCell.Offset(2).Select
    ActiveCell.Value = Q1

    End If
    
End Sub

 

[rlv01]

The underlying problem is that excel was never designed for large-group collaboration. I do a lot of this kind of collaboration in my day job. It sort of works in Teams but only if you only have just a few editors. To use excel for full-on team collaboration with a lot of contributors, you need to adopt a subversioning system like Tortoise SVN which enables a check-in, check-out system along with keeping an activity log of who did what, and when. Another alternative is to shift over to something like Smartsheets which was designed from the ground up for team collaboration.

 

[jasonb75]

The problem is, when two people click the + button and have the prompt open at the same time, the second person, and third, fourth and fifth person's answers will all be overwritten when the first person who clicked it finally hits enter and submits their response.

It's unlikely the multiple responses that are the problem, more like the delayed saving. Things like this generally only work if the code is programmed to save after every change, although 'forced saving' is rarely a good idea.

 

[chbrandt]

It's unlikely the multiple responses that are the problem, more like the delayed saving. Things like this generally only work if the code is programmed to save after every change, although 'forced saving' is rarely a good idea.

I thought that could be the case too, until we tried updating cells w/out the macro and it updated instantly. The problem seems to be that, while the InputBox is open, no updates come through from the other users, the sheet is essentially 'frozen', then when the response is put in, it overwrites anything that was entered while the box was open. Frustrating to say the least, but it could be that Excel just isn't built for this. Appreciate the responses.

 

[jasonb75]

The problem seems to be that, while the InputBox is open, no updates come through from the other users

That is very true, I hadn't considered response time.

With that in mind, I would personally start with something like this as a test point. The idea is that as soon as the code starts, it finds the empty cell, reserves it and saves the file. That way, if another user starts the same question, they will get the next cell in the column.

From personal experience, shared workbooks are notoriously unstable. In the event that 2 people try to save at the same time the process is likely to fail. Although having said that, that experience has been with legacy shared workbooks, if you're using the newer format then it may be more stable.

Sub Q1AddQuestion()

    Dim Q1 As Variant, rng As Range
    If IsEmpty(Range("B3")) Then Set rng = Range("B3") Else Set rng = Cells(Rows.Count, 2).End(xlUp).Offset(1)
    rng.Value = "reserved"
    ThisWorkbook.Save
    rng.Value = InputBox(Range("B1").Value)
    ThisWorkbook.Save
    
End Sub