Hi All,
I have created a Userform for a task project I am working on for my job. I am not a programmer and have limited knowledge of excel.
In essence, I have created a form that will be used by about 15-20 people for entering tasks they have completed. The details are then inserted into a worksheet and I can use that data for monitoring workloads and productivity. The problem I can sort of foresee is when multiple users try and enter userform data at the same time.
FYI - The code I have written works perfectly for 1 user.
The code I have used when you click on the 'add' button it finds the top most empty row and enters the details in the form. If more than one person opens the shared workbook at the same time, then each user will be entering their info and it will be inputted on the same row, overwriting each others form data. This is a problem.
The VBA code is below, again, i am not an expert and do not have access to databases. Its a simple project but I want it to work properly.
My question is, is there anyway to get around this problem with a shared workbook?
Thank you in advance for your help.
Neil
I have created a Userform for a task project I am working on for my job. I am not a programmer and have limited knowledge of excel.
In essence, I have created a form that will be used by about 15-20 people for entering tasks they have completed. The details are then inserted into a worksheet and I can use that data for monitoring workloads and productivity. The problem I can sort of foresee is when multiple users try and enter userform data at the same time.
FYI - The code I have written works perfectly for 1 user.
The code I have used when you click on the 'add' button it finds the top most empty row and enters the details in the form. If more than one person opens the shared workbook at the same time, then each user will be entering their info and it will be inputted on the same row, overwriting each others form data. This is a problem.
The VBA code is below, again, i am not an expert and do not have access to databases. Its a simple project but I want it to work properly.
Code:
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Report Entry")
'find first empty row in database
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'check if completed on xplan
If Trim(Me.chkCompleted.Value) = False Then
Me.chkCompleted.SetFocus
Me.lblCompleted.ForeColor = RGB(255, 0, 0) ' red
MsgBox "Has the report been completed on XPlan?"
Exit Sub
End If
'copy the data to the database
With ws
' .Unprotect Password:="password"
.Cells(lRow, 1).Value = Me.cboPara.Value
.Cells(lRow, 2).Value = Me.cboReport.Value
.Cells(lRow, 3).Value = Me.chkCashflow.Value
.Cells(lRow, 4).Value = Me.txtCeding.Value
.Cells(lRow, 5).Value = Me.chkAQ.Value
.Cells(lRow, 6).Value = Me.chkCompleted.Value
.Cells(lRow, 7).Value = Me.txtDate.Value
' .Protect Password:="password"
End With
'clear the data
Me.cboPara.Value = ""
Me.cboReport.Value = ""
Me.chkCashflow.Value = False
Me.txtCeding.Value = ""
Me.chkAQ.Value = False
Me.chkCompleted.Value = False
Me.cboPara.SetFocus
Me.lblCompleted.ForeColor = RGB(0, 0, 0) ' black
txtDate.Value = Now
txtDate = Format(txtDate.Value, "dd mmmm yyyy")
End Sub
Private Sub UserForm_Initialize()
Dim cPara As Range
Dim cReport As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupSheet")
For Each cReport In ws.Range("ReportType")
With Me.cboReport
.AddItem cReport.Value
End With
Next cReport
For Each cPara In ws.Range("Paraplanner")
With Me.cboPara
.AddItem cPara.Value
End With
Next cPara
txtDate.Value = Now
txtDate = Format(txtDate.Value, "dd mmmm yyyy")
Me.cboPara.SetFocus
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
My question is, is there anyway to get around this problem with a shared workbook?
Thank you in advance for your help.
Neil