Cancelling a sub from running so data doesn't get deleted

[dpaton05]

I have 2 buttons to copy data from the current workbook to another. One button copies and leaves the data there. The other button copies and deletes the data.

The files are on a network and if the second workbook is already open I have code that notifies the user trying to open the file and asks them to wait or contact the person who has it open.

The issue is that if the file is already open the user is asked to try again later but if they selected the button to copy the data and delete the contents, the contents will be gone and they won't be able to try again later as the data won't be there.

I have a rather lengthy sub to copy the data as there are many processes that need to be run.

This is the code for my copy and delete contents button:

Sub cmdCopyLineBlank_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Costing_tool")

    ws.Unprotect
        Call cmdCopy
    ws.Unprotect
        Call CostingDeleteAll
  '  ws.Protect

End Sub

and this code is from the second workbook

Sub Workbook_Open()

Application.WindowState = xlMaximized

Dim file1 As Integer
Dim strLine As String
file1 = FreeFile
    If Not ActiveWorkbook.ReadOnly = True Then
        'only add name to the usage log if the user has it locked
        Open ThisWorkbook.Path & "\usage.log" For Append As #file1
        Print #file1, Environ("USERNAME") & ". Please close all the additional workbooks that will be opened " _
        & " WITHOUT SAVING THEM. Then contact the user that has it open or wait until they are finished."
        Close #file1
    Else
        'if someone else has the file open, find out who
        Open ThisWorkbook.Path & "\usage.log" For Input Access Read As #file1
            Do While Not EOF(file1)
               Line Input #file1, strLine
            Loop
        Close #file1
        MsgBox "The following user has the allocation sheets open: " & strLine
    End If


'Worksheets("home").Protect Password:="costings"

End Sub

Part of cmdCopy calls the second workbook to open but if it is already open by someone else, how do I force it to skip the line of code?

Call CostingDeleteAll

 

[jasonb75]

Sorry, I realise after seeing your post that my reply was anything but clear. I've added examples of what is needed but please see the last line of this post before applying them.

You should keep the original line of If UnsafeToDelete = True Then Exit Sub after the line to open the first workbook.

Then you would need this after the line to open "Report Tracking.xlsm".

If UnsafeToDelete = True Then
    Workbooks("Allocation Sheet.xlsm").Close False  
    Exit Sub  
End If

In summary, the block above should be closing any workbooks that were not in read only (i.e. all except the one in the line directly above, as that will be closed by the workbook open event detecting that it is read only).

One thing that I have just noticed is that your code is checking to see if the files are already open before trying to open them, if the user already had the allocation sheet workbook open then this would still close it without saving their changes if the report tracking workbook is read only, so a change may be needed to allow for that depending on requirement.

 

[dpaton05]

If any workbook is already open, work allocation sheet or report tracking sheet, I don't want to lose anything if they have to close and there are changes since it was last saved. How do I change the code so I will not lose anything?

 

[jasonb75]

Using

If UnsafeToDelete = True Then
    Workbooks("Allocation Sheet.xlsm").Close True  
    Exit Sub  
End If

after the line to open the report tracking sheet would save the allocation sheet before closing if it is being closed as a result of the report sheet being read only. That is the only one that would need to be different, any other closures would be of the workbook that is read only so saving wouldn't be needed.

Many users are against forced saving in such situations, if you would prefer it, it should be possible to check if the workbook was already open or if it was opened by the code and leave it in the same state when the code finishes. I would need to go back over the thread and check each of the code procedures to be sure that the changes will not conflict though. If that is needed then I would prefer to do it nearer to the weekend when I will have enough free time to do it without being interrupted and hopefully get the whole thing right first time.

 

[dpaton05]

I'm sorry I Jason, I gave you the wrong idea. I don't need it to force save, I just need to somehow, skip the delete all code if the second file is in read only mode?

 

[jasonb75]

Without applying the change in my last reply it should work as follows.

If the first file (allocation) is read only then it should be closed automatically by the workbook open code.
If the second file (report) is read only then it should be closed automatically by the workbook open code and the first file (allocation) should also be closed by the code in post 31 that was edited into the cmdCopy code.

The grey area is what should be done if the user already had the allocation file open in read / write before running the code? In this situation the current code would still close the allocation file without saving if the report file was found to be read only. I can change it so that the file will be allocation file will be left open if it was already open at the start of the code run, but as I mentioned above, I would need to go through the code carefully to be sure that one change is not going to conflict with another.

To be honest, I didn't think far enough ahead with the first changes. I'm going to set up a series of test files to try a couple of different approaches before suggesting any more changes to the code so that it doesn't take another week of things not working as needed.

Thinking ahead a bit more this time, does the CostingDeleteAll procedure attempt to open any workbooks, or is all of the opening done in the cmdCopy procedure?

 

[dpaton05]

This is starting to do my head in. I have just thought of a simple way I can get it done Jason, I will have a button to delete the rows and a button to copy the rows. That will allow the user to be alerted if the file is already open and still have the data there to do the copy at a later stage as the alert occurs in cmdcopy.

I am on holidays at the moment so I can't actually test it but l think it should work. Thanks for the effort you have already put in to try and help me solve this problem. I will let you know how it goes.

 

[dpaton05]

I am at work now and it works perfectly using the procedure described in the previous post. Thank you for you efforts in trying to help me solve it.

 

[jasonb75]

You're welcome!

Glad you found a solution that worked in the end

 

[dpaton05]

Could you please send me a private message with the url of this thread in it?

 

[jasonb75]

Tried to but think you have blocked private messages, it's not letting me send one.

You should be able to get the url from your address bar or by right clicking the thread link on your watched threads page.