lickrob
01-30-2013, 07:26 AM
Firstly, Yes, i know Shared Workbooks are no good, however i am unable to use access (or any other Database) in any form so im stuck with a shared workbook.
Basically i have a front end that will add the contents of a userform to a shared workbook.
Code to add to shared WB:
Dim LastCell As String
Dim lol As String
Dim msgerr As String
Dim lolff As Range
If tbCust.Text = "" Or tbCons.Text = "" Or tbTo.Text = "" Or tbFrm.Text = "" Then
MsgBox ("All Fields Must Be Completed")
Else
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'activate new wb could get rid of this maybe if change cells below
Workbooks("NewWB").Activate
Application.DisplayAlerts = False
On Error GoTo err
If Workbooks("NewWB.xls").MultiUserEditing Then
Workbooks("NewWB.xls").AcceptAllChanges
Workbooks("NewWB.xls").Save
End If
Set lolff = Workbooks("NewWB").Sheets(1).Range("A35536").End(xlUp).Offset(1, 0)
LastCell = lolff.Row
'creates the if formula with correct cells to check dates and save in lol string
lol = "=IF(AND(TODAY()>=C" & LastCell & ",TODAY()<=D" & LastCell & "),""Yes"",""No"")"
Application.AlertBeforeOverwriting = False
Cells(LastCell, 1) = tbCust.Text
Cells(LastCell, 2) = lol 'if formula
Cells(LastCell, 3) = CDate(Me.tbFrm.Value)
Cells(LastCell, 4) = CDate(Me.tbTo.Value)
Cells(LastCell, 5) = tbCons.Text
Workbooks("LOA Search").Activate
'save and close NewWB
'Workbooks("NewWB.xls").AcceptAllChanges
Workbooks("NewWB.xls").Save
Workbooks("NewWB.xls").Close
Application.DisplayAlerts = True
'close UserForm
Unload Me
Application.ScreenUpdating = True
End If
End
err:
Application.DisplayAlerts = False
Workbooks("NewWB.xls").Saved = True
Workbooks("NewWB.xls").Close
Application.DisplayAlerts = True
msgerr = MsgBox("An Error Has Occoured" & vbCrLf & "Please Try Again", vbCritical, "Error:")
Unload Me
Application.ScreenUpdating = True
the shared workbook is opened when the userform loads due to the fact that there is an autocomplete on one of the textbox's which requires the shared workbook to be open.
My Problem is when this workbook is opened by 2 poeple at the same time. the second person to save the workbook will get the conflict resoultion popup and have to choose to accept other or their own changes. i do not want this to happen.
Any ideas?
Thanks
Rob
Basically i have a front end that will add the contents of a userform to a shared workbook.
Code to add to shared WB:
Dim LastCell As String
Dim lol As String
Dim msgerr As String
Dim lolff As Range
If tbCust.Text = "" Or tbCons.Text = "" Or tbTo.Text = "" Or tbFrm.Text = "" Then
MsgBox ("All Fields Must Be Completed")
Else
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'activate new wb could get rid of this maybe if change cells below
Workbooks("NewWB").Activate
Application.DisplayAlerts = False
On Error GoTo err
If Workbooks("NewWB.xls").MultiUserEditing Then
Workbooks("NewWB.xls").AcceptAllChanges
Workbooks("NewWB.xls").Save
End If
Set lolff = Workbooks("NewWB").Sheets(1).Range("A35536").End(xlUp).Offset(1, 0)
LastCell = lolff.Row
'creates the if formula with correct cells to check dates and save in lol string
lol = "=IF(AND(TODAY()>=C" & LastCell & ",TODAY()<=D" & LastCell & "),""Yes"",""No"")"
Application.AlertBeforeOverwriting = False
Cells(LastCell, 1) = tbCust.Text
Cells(LastCell, 2) = lol 'if formula
Cells(LastCell, 3) = CDate(Me.tbFrm.Value)
Cells(LastCell, 4) = CDate(Me.tbTo.Value)
Cells(LastCell, 5) = tbCons.Text
Workbooks("LOA Search").Activate
'save and close NewWB
'Workbooks("NewWB.xls").AcceptAllChanges
Workbooks("NewWB.xls").Save
Workbooks("NewWB.xls").Close
Application.DisplayAlerts = True
'close UserForm
Unload Me
Application.ScreenUpdating = True
End If
End
err:
Application.DisplayAlerts = False
Workbooks("NewWB.xls").Saved = True
Workbooks("NewWB.xls").Close
Application.DisplayAlerts = True
msgerr = MsgBox("An Error Has Occoured" & vbCrLf & "Please Try Again", vbCritical, "Error:")
Unload Me
Application.ScreenUpdating = True
the shared workbook is opened when the userform loads due to the fact that there is an autocomplete on one of the textbox's which requires the shared workbook to be open.
My Problem is when this workbook is opened by 2 poeple at the same time. the second person to save the workbook will get the conflict resoultion popup and have to choose to accept other or their own changes. i do not want this to happen.
Any ideas?
Thanks
Rob