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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,922
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,593
Messages
5,487,751
Members
407,610
Latest member
bellakim00

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top