VBA Shared Workbook Save issue

[gman1979]

Hi Guy’s,

I have created a userform where the user is required to enter data which they can then save. I’ve got the save button writing the information entered into the textboxes into another xl workbook (using this as a reference file).

This workbook is a shared workbook so that multiple users can access it at the same time. Because a shared workbook updated with previously saved details that may have been entered by another user, when the user selects the save button on my userform my code saves the workbook before saving the data that has been entered into the workbook.

This was to try an eliminate data being accidentally overwritten. I’ve made up a mini workbook to try and solve this and tested it with one of my colleagues. When we both selected save at the exact same time, one of us got the “save complete” message box, the other got the option to accept the other users data or overwrite it with their own.

I was hoping that I could incorporate into my code a loop that recognises the workbook is being written to and then tries again a set number of times before advising the user to try again.

I have heard about a try again command but don't know much about it.

Below is the code in my mini userform, any help would be greatly appreciated.

Thanks in advance Guy’s

G

Private Sub CommandButton1_Click()
Dim RowCount As Long
Dim ctl As Control
' Check user input before writing to worksheet
If Me.TextBox1.Value = "" Then
MsgBox "Please enter a name.", vbExclamation, "Amey Structures Database"
Me.TextBox1.SetFocus
Exit Sub
End If
If Me.TextBox2.Value = "" Then
MsgBox "Please enter an age.", vbExclamation, "Amey Structures Database"
Me.TextBox2.SetFocus
Exit Sub
End If

'save workbook before writing data into it.
Workbooks("MutliSaveBook.xls").Activate
ActiveWorkbook.Save
Set LastRow = Worksheets("Sheet1").Range("a65536").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
ActiveWorkbook.Save

'once written display confirmation message
MsgBox "Details Saved", vbInformation, "Amey Sructures Databse"

'clear entries affter save complete
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = Time
Me.Repaint
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = Time
End Sub