Sheet used by multiple users

Voltz

New Member
Joined
May 6, 2014
Messages
26
HI to All,

I have been forllowing and learning for a long time but now i'm stack on my own and would appreciate your help.

I have a workbook (jobsheet.xlsm) that is used by 3 users, inputting data in there individual form and with a code data will be sent into the Jobsheet. So far so good, but if for any reason 1 of the user open the Jobsheet to check or edit something into it and while the sheet is open another user input more data, the new data will not be saved since it will be opened on another PC.
All workbooks including Jobsheet.xlsm is on a mapped Drive "T".

Is there a work around to make fool proof?

part of the code


Rich (BB code):
 [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub copy_to_print_Jobsheet()
'
' Macro5 Macro
'
    Application.ScreenUpdating = False
'
    Range("a7:n7").Offset(Range("m2").Value, 0).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Copy
    Workbooks.Open "t:\Users\Documents\Orders\jobsheet.xlsm"
    Worksheets("JSH PRINT").Activate
    Range("A1").Select
    Selection.End(xlDown).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Color = -16776961
         .TintAndShade = 0
            .Weight = xlThick
    End With
    Workbooks("jobsheet.xlsm").Save
    Workbooks("jobsheet.xlsm").Close
    Range("a8").Select
    Range("M6").Select
    Range("M6").Value = "Order Saved"
    Range("m1").Select
    Selection.Copy
    Range("L5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Protect
    
    
    
End Sub

Thanks

[/FONT]
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,916
Office Version
2019
Platform
Windows
but if for any reason 1 of the user open the Jobsheet to check or edit something into it and while the sheet is open another user input more data, the new data will not be saved since it will be opened on another PC.

Is there a work around to make fool proof?

Hi,
If you are using your JobSheet workbook as a database which your users write to via a template workbook, the database workbook as you discovered, cannot be opened in read / write mode whilst users need to write to it.

Not fool proof but you can reduce conflicts by providing an edit function in the template workbooks where users can call back a record. You need to develop code that would briefly open the database workbook in readonly mode, find and return the required record. Users can then edit it in their workbook and then write back the updates to the database.

Dave
 

Voltz

New Member
Joined
May 6, 2014
Messages
26
Hi,
If you are using your JobSheet workbook as a database which your users write to via a template workbook, the database workbook as you discovered, cannot be opened in read / write mode whilst users need to write to it.

Not fool proof but you can reduce conflicts by providing an edit function in the template workbooks where users can call back a record. You need to develop code that would briefly open the database workbook in readonly mode, find and return the required record. Users can then edit it in their workbook and then write back the updates to the database.

Dave
Hi Dave,

That may be an option yes.

Thanks,
 

Watch MrExcel Video

Forum statistics

Threads
1,102,134
Messages
5,484,929
Members
407,474
Latest member
Pam Sander

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top