resolving conflicts message

[stantonp]

Hi

I have a shared workbook with a VBA userform that copies information users complete into a worksheet. this works great when the userform is opened only by one person at a time, however if multiple users are in the form the last person to press send gets the resolving conflicts message, asking them to pick which data to copy to the sheet.

Does anyone know i would allow multiple user to send there data without getting the conflicts message.

'send via the Form send button the completed information to New'
Private Sub SendButton_Click()
On Error Resume Next
Dim iRow As Long
Dim ws As Worksheet
Dim textBoxes() As Textbox
'finds the name of the New'
Set ws = Worksheets("New")
 
 
'find first empty row in database'
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
 
'check for a Name'
If Trim(Me.TextBox1.Value) = "" Then
Me.TextBox1.SetFocus
MsgBox "Please complete name feild "
Exit Sub
End If
'check for a Tel Ext'
If Trim(Me.TextBox2.Value) = "" Then
Me.TextBox2.SetFocus
MsgBox "Please complete Telephone extension on the form"
Exit Sub
End If
'check for a section'
If Trim(Me.Textbox3.Value) = "" Then
Me.Textbox3.SetFocus
MsgBox "Please complete your section on the form"
Exit Sub
End If
'check for a suggestion'
If Trim(Me.Textbox4.Value) = "" Then
Me.Textbox4.SetFocus
MsgBox "Please enter your suggestion or question"
Exit Sub
End If
 
'copy the data to the New'
ws.Cells(iRow, 1).Value = Date
ws.Cells(iRow, 2).Value = Me.TextBox1.Value
ws.Cells(iRow, 3).Value = Me.TextBox2.Value
ws.Cells(iRow, 4).Value = Me.Textbox3.Value
ws.Cells(iRow, 5).Value = Me.Textbox4.Value
 
MsgBox "Your suggestion has been sent -   Please check the suggestion or question board for replies to your idea", vbOKOnly + vbInformation, ""
 
'clear all the data from the Add form'
 
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.Textbox3.Value = ""
Me.Textbox4.Value = ""
 
'sets focus on the first text box'
Me.TextBox1.SetFocus
ThisWorkbook.Save
 
'Email to sender
 
  'Email is sent to admin when send button is pressed"
    Const olMailItem = 0
    Const olFolderOutbox = 4
 
        Email_Subject = "A suggestion has been submitted via the suggestion portal"
        Email_Send_From = ""
        Email_Send_To = "test@test.com"
        Email_Cc = ""
        Email_Bcc = ""
        Email_Body = "A suggestion or question has been submitted  "
 
        On Error GoTo debugs
 
        Set olApp = CreateObject("Outlook.Application")
        olApp.Session.GetDefaultFolder olFolderOutbox
 
        With olApp.CreateItem(olMailItem)
            .Subject = Email_Subject
            .To = Email_Send_To
            .cc = Email_Cc
            .BCC = Email_Bcc
            .Body = Email_Body
            .Send
        End With
 
debugs:
    If Err <> 0 Then
        MsgBox "Run-time error '" & Err.Number & "':" _
        & vbCrLf & vbCrLf & Err.Description
    End If
 
 
End Sub

 

[Logit]

.
The question of a "Shared" workbook is asked quite often. The short answer is ... Excel is not designed to be shared even though you see that setting in the menu.

The closest you can get to solving your issue using only Excel as you have it set up now, is to have each user store a copy of the worksheet on their computer where they can access and complete without interference from anyone else trying to work in the same workbook. Your MASTER workbook can be set up to 'poll' each of those individual workbooks and copy the information to the MASTER workbook.

An alternative would be to use MS CLOUD service (or someone else's cloud service) that is set up to run a master copy of an EXCEL workbook that numerous users can access at the same time. MS CLOUD is designed to do that for you.

Short of the above ... you'll need to give each user a set time and date when they can access the MASTER workbook to enter their data.

 

[stantonp]

Thank you for your reply

Our network is cloud based and has shared folders which everyone has access too. The workbook is in one of these folder which everyone will have a shortcut on desktop to.

Do you know a way of writing VBA when "resolve conflict" message is prompted the code will ignore the message then inserted that data into the next empty row?

 

[Logit]

Sorry but I am not familiar with a Cloud setup ... cannot assist you there.

I'm curious what the Cloud administrator has to say about the issue. It almost sounds like it is not an issue with your shared workbook but with their Cloud system ?

 

[stantonp]

Sorry I didn't make myself clear. what I really mean is that all the programs, storage including windows 7 are on servers someone else in the country, this virtual system is call citrix. Its not the stablest of systems but that's what our department uses.

One of the solutions I am trying which will work but is not Ideal is I have changed the code once a user presses send a copy of the workbook is saved with today's date and time into another folder. Once I get the email I can go into every saved sheet and copy the data back to the main user workbook so everyone can see the posts.