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.
VBA Code:
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