Excel VBA on server

[vipuldawar88]

Hello Everyone.

I am relatively a new to this VBA language.
Actually I have made a macro linked to excel.and the macro form enters the data into a excel worksheet.

Now problem is that,the Worksheet is shared on the server and Multiple user are entering data at the same time.

So how can I synchronize the Data entry by every user.that they all enter at next empty row without conflicting and over writing the Text on the Particular row.

Below is the code of my Macro


Private Sub cmdadd_Click()

Dim iRow As Long
Dim ws As Worksheet
Dim var As Double

Set ws = Worksheets("testsheet")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
'check for a part number
If Trim(Me.nm.Value) = "" Then
Me.nm.SetFocus
MsgBox "Please enter consultant name"
Exit Sub
End If
var = (Val(csap.Value) / 100) * Val(br.Text)
csa.Text = var
var = (Val(rbp.Text) / 100) * Val(br.Text)
rebate.Text = var
var = Val(br.Text) - Val(csa.Text) - Val(rebate.Text)
nbr.Text = var
'copy the data to the database
ws.Cells(iRow, 2).Value = Me.nm.Value
ws.Cells(iRow, 3).Value = Me.remarks.Value
ws.Cells(iRow, 4).Value = Me.br.Value
ws.Cells(iRow, 5).Value = Me.csa.Value
ws.Cells(iRow, 6).Value = Me.rebate.Value
ws.Cells(iRow, 7).Value = Me.nbr.Value
ws.Cells(iRow, 8).Value = Me.pr.Value
ws.Cells(iRow, 9).Value = Me.empcon.Value
ws.Cells(iRow, 10).Value = Me.doj.Value
ws.Cells(iRow, 11).Value = Me.proj.Value
ws.Cells(iRow, 29).Value = Me.pon.Value
ws.Cells(iRow, 36).Value = Me.recr.Value
ws.Cells(iRow, 37).Value = Me.sp.Value
ws.Cells(iRow, 38).Value = Me.vn.Value


End Sub
Private Sub cmdclear_Click()
'clear the data
Me.br.Value = ""
Me.csa.Value = ""
Me.pr.Value = ""
Me.nbr.Value = ""
Me.rebate.Value = ""
Me.empcon.Value = ""
Me.doj.Value = ""
Me.proj.Value = ""
Me.pon.Value = ""
Me.recr.Value = ""
Me.sp.Value = ""
Me.vn.Value = ""
Me.nm.Value = ""
Me.remarks.Value = ""
Me.rbp.Value = ""
Me.csap.Value = ""
Me.br.SetFocus
End Sub
Private Sub cmdclose_Click()
Unload Me
End Sub
Private Sub csap_Click()
var = (Val(csap.Value) / 100) * Val(br.Text)
csa.Text = var
End Sub
Private Sub ListBox1_Click()
var = (Val(csap.Value) / 100) * Val(br.Text)
csa.Text = var
End Sub
Private Sub rbp_Change()
var = (Val(rbp.Text) / 100) * Val(br.Text)
rebate.Text = var
var = Val(br.Text) - Val(csa.Text) - Val(rebate.Text)
nbr.Text = var
End Sub