VBA Variable Being Overwritten When Collaborating

chbrandt

New Member
Joined
Oct 14, 2011
Messages
2
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

rlv01

Well-known Member
Joined
May 16, 2017
Messages
890
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
13,230
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 14, 2011
Messages
2
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

Well-known Member
Joined
Dec 30, 2008
Messages
13,230
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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
 

Forum statistics

Threads
1,137,154
Messages
5,679,912
Members
419,862
Latest member
Bluewings666

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top