# Multiple users to input data at the same time, using a form



## bolesey (Feb 18, 2011)

Hi All,


I've made a spreadsheet to record sales in the office. The idea was that I'd make a form to input data into a spreadsheet and that all staff members (~100 at any one time) would have the spreadsheet open and input the data after each sale.


The problem I'm having is that when multiple users open the spreadsheet, it always inputs the data over what another user has already inserted. However, if one user is logged in, it will work fine and insert in a new row every time. In other words, the spreadsheet doesn't seem to work in real time, so it doesn't seem to realise when someone else has inserted data.


Is there any way around this? I have been advised the best thing to do may be to start again using the database Access? I'd rather avoid that!!

I've pasted below the code for when a user clicks the 'insert data' button.


Thanks!!


Private Sub cmdInput_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Input")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row



'copy the data to the database
ws.Cells(iRow, 1).Value = VBA.Date
ws.Cells(iRow, 2).Value = Me.txtManName.Value
ws.Cells(iRow, 3).Value = Me.txtAgentName.Value
ws.Cells(iRow, 4).Value = Me.txtCustNo.Value
ws.Cells(iRow, 5).Value = Me.txtEmailRef.Value
ws.Cells(iRow, 9).Value = Me.txtX1Sales.Value
ws.Cells(iRow, 10).Value = Me.txtX2Sales.Value
ws.Cells(iRow, 11).Value = Me.txtX3Sales.Value
ws.Cells(iRow, 13).Value = Me.txtX4Sales.Value


'Product Type

If chkCHC = True Then
ws.Cells(iRow, 6).Value = "X1
End If
If chkX2 = True Then
ws.Cells(iRow, 6).Value = "X2"
End If
If chkX3 = True Then
ws.Cells(iRow, 6).Value = "X3"
End If




'Sale Recorded - Yes or No?

If optSaleYes = True Then
ws.Cells(iRow, 7).Value = "1"
End If
If optSaleYes = False Then
ws.Cells(iRow, 7).Value = "0"
End If

If optSaleNo = True Then
ws.Cells(iRow, 8).Value = "1"
End If

'(REMOVED)If optSalesNo = False Then
'ws.Cells(iRow, 8).Value = "1"
'End If






'clear the data

Me.txtManName.Value = ""
Me.txtAgentName.Value = ""
Me.txtCustNo.Value = ""
Me.txtEmailRef.Value = ""
Me.txtX1Sales.Value = "0"
Me.txtX2Sales.Value = "0"
Me.txtX3Sales.Value = "0"
Me.txtX4Sales.Value = "0"
Me.txtManName.SetFocus
End Sub


----------



## RSpecianjr (Jan 20, 2010)

Hey Bolesey,

Honestly the best method to store data for multiple users to use at once is going to be a database, such as Access. That is not to say it cannot be done in Excel, it just becomes complicated.

As long as you can have multiple people edit the file at once, you will need to setup a method for people to enter data into their own fields. Once you have that you can have another system to piece those fields together. 

Off the top of my head, I would create a separate workbook for the data they input. Any time the file is saved, it will open up the new workbook, find the end of the data, put in the data, then save/close it. You can then do a lookup off this workbook or create a macro to open/extrapolate/close the workbook. You will have to make sure you don't allow multiple people into the archive workbook at the same time. That can be handled in the VBA to just wait a few seconds then try again.

Excel is not good at allowing multiple people to make changes at once, it isn't what it was designed for. Access is easy to learn and I highly recommend it. You can even still use Excel as the "input mechanism" and have Excel forward the data to Access.

Ciao,

Robert D. Specian Jr.


----------

