Multiple users using same user form having errors and slow at times

[Msears]

Hello All, I am using a spreadsheet with several different user forms, depending on the program info being entered. All are setup in a similar fashion, so I will only reference one of them here. The way I have it setup is that when data is entered it will put the data on the first empty row, no problem when only one user is doing this at a time. Each form goes to a different sheet, but when more than one user is entering on the same issue it tends to overwrite the data being entered by the other user. I started having the sheet "save" multiple times throughout the process, which has caused some significant lag and some errors like "locked by another user", if saved at the exact same time. There are some formulas that are being used on each of the sheets that isn't being handled by the VBA user form itself, which to causes some lag, if the data is being overwritten by another user. Question #1 is there a method that would say lock the first unused line when that user form is selected and would do so until the data is fully entered? For instance, if 10 users are using that form and that sheet, when it is initialized, it would place a lock on that row until completed and do so for each of the users, until the form is closed. Question #2 is regarding to speed. The current code saves before and after, but also when each sheet is activated, I know this slows down a lot, so maybe if the code can be redone, I won't have to have it save so much to prevent the errors. Thanks in advance. MS

Private Sub EnterButton_Click()
Dim Last_Row As Long
Dim i As Long, lr As Long

Application.ScreenUpdating = False

ActiveWorkbook.Sheets("Non SWAP").Activate
Range("B2").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ThisWorkbook.Save

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1


'Transfer information
Cells(emptyRow, 2).Value = ClientComboBox1.Value
Cells(emptyRow, 6).Value = ServiceComboBox2.Value
Cells(emptyRow, 8).Value = DateTextBox.Value
Cells(emptyRow, 10).Value = ApptTextBox1.Value
Cells(emptyRow, 11).Value = TotalTextBox2.Value
Cells(emptyRow, 12).Value = ComboBox1.Value
Cells(emptyRow, 14).Value = ComboBox2.Value
Cells(emptyRow, 16).Value = ComboBox3.Value
Cells(emptyRow, 18).Value = ComboBox4.Value
Cells(emptyRow, 20).Value = ComboBox5.Value
Cells(emptyRow, 22).Value = ComboBox6.Value
Cells(emptyRow, 24).Value = ComboBox7.Value
Cells(emptyRow, 26).Value = ComboBox8.Value
Cells(emptyRow, 13).Value = TimeTextBox1.Value
Cells(emptyRow, 15).Value = TimeTextBox2.Value
Cells(emptyRow, 17).Value = TimeTextBox3.Value
Cells(emptyRow, 19).Value = TimeTextBox4.Value
Cells(emptyRow, 21).Value = TimeTextBox5.Value
Cells(emptyRow, 23).Value = TimeTextBox6.Value
Cells(emptyRow, 25).Value = TimeTextBox7.Value
Cells(emptyRow, 27).Value = TimeTextBox8.Value
Cells(emptyRow, 28).Value = NotesTextBox.Value
Cells(emptyRow, 29).Value = PodComboBox9.Value


If AbsentCheckBox.Value = True Then Cells(emptyRow, 31).Value = AbsentCheckBox.Caption
If StaffOutCheckBox1.Value = True Then Cells(emptyRow, 31).Value = StaffOutCheckBox1.Caption
If NoShowCheckBox2.Value = True Then Cells(emptyRow, 31).Value = NoShowCheckBox2.Caption
If CanceledCheckBox3.Value = True Then Cells(emptyRow, 31).Value = CanceledCheckBox3.Caption
If WeatherCheckBox4.Value = True Then Cells(emptyRow, 31).Value = WeatherCheckBox4.Caption
If HolidayCheckBox5.Value = True Then Cells(emptyRow, 31).Value = HolidayCheckBox5.Caption

ThisWorkbook.Save

Application.ScreenUpdating = True

End Sub

 

[Micron]

If you research this I think you'd get the drift that Excel is not really for concurrent users for the same workbook and that Access is the ticket. You probably won't want to go there because to do that well requires a lot of up front learning.

When you post code, please use code tags (vba button on forum posting toolbar) to maintain your indentation and readability. Old curmudgeons like me hate reading code like that (and' maybe won't).
EDIT - I say that about Excel/Access because it's my understanding that the main issue is concurrent data input on the same sheet, not that concurrent users can't use the same form.

 

[Joe4]

I agree with Micron. You are probably using the wrong tool for the job.
Programs like Microsoft Access are much better equipped to handle concurrent users than Excel is.