I have a workbook that launches a userform upon opening. It is essentially a scorecard for supervisors to use for taking notes on the employees on their team. When selecting an employee's name from the combobox, it populates a bunch of labels with metrics for the employee. At the bottom I have a ListView control that is filled with notes that have been entered on the agent in the past.
Originally, I had a textbox above the ListView where you could type out a note and click a command button to enter a "record" on another worksheet containing the employee's name, the note, and a timestamp of when the button was clicked. However, issues came up when a supervisor pointed out that a note they entered did not show up on the scorecard ListView for another supervisor with the same file/workbook/scorecard open.
Is there anyway to use a separate Excel workbook on a network drive as a central database? So when one supervisor enters a note, it writes it to that workbook, saves the workbook, and then it can show up on another copy of the scorecard? I tried creating a Shared workbook, since it may be feasible that two supervisors could enter a note at the same time and would need the workbook eligible to be edited by multiple users. However, this did not work.
Here is the code for when you press the command button to add the note. The xlw variable is the file path on the network drive for where I have the dbNotes workbook "database", which is Shared.
Any ideas would be greatly appreciated.
Thank you
Originally, I had a textbox above the ListView where you could type out a note and click a command button to enter a "record" on another worksheet containing the employee's name, the note, and a timestamp of when the button was clicked. However, issues came up when a supervisor pointed out that a note they entered did not show up on the scorecard ListView for another supervisor with the same file/workbook/scorecard open.
Is there anyway to use a separate Excel workbook on a network drive as a central database? So when one supervisor enters a note, it writes it to that workbook, saves the workbook, and then it can show up on another copy of the scorecard? I tried creating a Shared workbook, since it may be feasible that two supervisors could enter a note at the same time and would need the workbook eligible to be edited by multiple users. However, this did not work.
Here is the code for when you press the command button to add the note. The xlw variable is the file path on the network drive for where I have the dbNotes workbook "database", which is Shared.
Code:
Private Sub cmdAddNote_Click()
Dim lastrow As Integer
Dim lastrow2 As Integer
Dim xl0 As New Excel.Application
Dim xlw As New Excel.Workbook
Set xlw = xl0.Workbooks.Open("\\prod\test\Scorecard\Notes\dbNotes.xlsx")
'Helps speed up the processing
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.DisplayStatusBar = False
End With
'Removes the current agent's notes from the ListView control.
With lsvNotes
.ColumnHeaders.Clear
.ListItems.Clear
End With
'Error handling for if supervisor tries to add a note without
'typing anything in the notes textbox and has no agent selected.
If Me.txtNotes.Value = "" And Me.cboChooseAgent.Value = "" Then
MsgBox "You must select an agent " & vbNewLine & "and add a note."
ElseIf Me.cboChooseAgent.Value = "" Then
MsgBox "You must select an agent."
ElseIf Me.txtNotes.Value = "" Then
MsgBox "There is no note to be added."
Else
'sets lastrow2 to be the end of the recordset on worksheet(database)
'puts the agent's name in the first column of the record
'the note in the second column of the record
'and the timestamp of the button press in the third column
'it quickly saves and closes the dbNotes workbook
'and clears the variables
lastrow2 = xl0.Sheets("Notes").Range("A1").CurrentRegion.Rows.count + 1
xl0.Sheets("Notes").Cells(lastrow2, 1) = Me.cboChooseAgent.Value
xl0.Sheets("Notes").Cells(lastrow2, 2) = Me.txtNotes.Value
xl0.Sheets("Notes").Cells(lastrow2, 3) = Format(Now, "mm/dd/yy hh:mm")
xlw.Save
xlw.Close
Set xl0 = Nothing
Set xlw = Nothing
End If
'blanks out the notes textbox
Me.txtNotes.Value = ""
'refills the ListView with all of the current notes for the agent.
Call filllsvNotes
'turns all of these back on.
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayStatusBar = True
End With
End SubAny ideas would be greatly appreciated.
Thank you
