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]

The way that I would do it (based on the information provided) would be to add a public boolean variable to a standard module in the workbook with the button.
Public SafeToDelete As Boolean

Then add the line SafeToDelete = True inside the block If that identifies the other file as being open by another user.

Finally, add the line If SafeToDelete = True Then SafeToDelete = False: Exit Sub at the point where you want to stop the code running.
Normally I would use End instead if Exit Sub, but that can have undesired consequences.

 

[dpaton05]

Thanks but I already found out how I can do it. I just wrote End

 

[jasonb75]

I just wrote End

See the last line of my earlier post.

I would advise only using End if it is a file that only you will be using. For files shared with other users you can potentially cause them unknown errors with other workbooks.

 

[dpaton05]

Using End, my procedure seemed to work properly, but I used your suggestion and it didn't stop the code from running. It was meant to stop additional workbooks from opening as I have open code that runs that opens other workbooks but if someone else has the file open, I don't want those other workbooks opening.

As I said, End seemed to stop them from opening but your code doesn't stop the code from running.

What I did was I added

Public SafeToDelete As Boolean

at the top of module 1 and updated my open code. Does the code look right?

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 any allocation sheets that has been 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
        'Assign true to the SafeToDelete variable to identify someone has it open
        SafeToDelete = True
        MsgBox "The following user has the allocation sheets open: " & strLine
        'If SafeToDelete is true, a user has the allocation sheet open, so exit sub
        If SafeToDelete = True Then SafeToDelete = False: Exit Sub
    End If


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

End Sub

I even tried other places to put the code but still it didn't exit the procedure and still opened the additional workbooks.

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 any additional sheets that has been opened" _
        & " WITHOUT SAVING THEM. Then contact the user that has it open or wait until they are finished."
        Close #file1
        SafeToDelete = True
    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
        'Assign true to the SafeToDelete variable to identify someone has it open
        
        MsgBox "The following user has the allocation sheets open: " & strLine
        'If SafeToDelete is true, a user has the allocation sheet open, so exit sub
        If SafeToDelete = True Then SafeToDelete = False: Exit Sub
    End If


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

End Sub

 

[jasonb75]

Your first one looks correct at a glance, I'll look at it in more detail and set up a quick test file when I get home later.

To see the reasons why I said it was a bad idea, take a look at the Remarks section of the page linked below. It is possible that you could use End and never have any problems, but if you do suddenly encounter problems that were not there previously then it might give you some idea of the cause.

End statement (VBA)

Office VBA reference topic

docs.microsoft.com

 

[dpaton05]

Thank you. Do you think I could use stop?

 

[jasonb75]

Stop is a bit misleading, it's more of a pause. It is basically allows the code to run up to the point of the Stop line then passes control to you to step through the remaining code manually with f8 but it doesn't end the process.

The main point to consider is the part that refers to public variables, anything that will be deleted / cleared / closed by using End. If there is nothing there that is going to cause you any issues then using End should be fine. Otherwise a bit of perseverance with the public boolean method will be needed.

 

[dpaton05]

Thanks for that explanation.

 

[jasonb75]

I think I had one of those moments when I looked at your code in post 5
The Exit Sub part needs to go in the calling procedure. Also, the declaration in module 1 needs to be in the workbook with the calling procedure, not the one with the workbook open event.
SafeToDelete = True still needs to be set to true in the workbook open event as per your first try in post 5, although I should have referred to it as UnsafeToDelete given that when it is true the data should be kept.

When you have nested procedures (calling one from another) the line If SafeToDelete = True Then Exit Sub should be added to each procedure except for the one that started things rolling, which should have the line If SafeToDelete = True Then SafeToDelete = False: Exit Sub

Exit sub only exits the current procedure, not all running procedures so the variable needs to stay True until you're exiting the last one. I'm pressed for time at the moment but I'll post a simple example with 3-4 procedures later to help you follow how it works.

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

    ws.Unprotect
        Call cmdCopy

        If SafeToDelete = True Then SafeToDelete = False: Exit Sub
    ws.Unprotect
        Call CostingDeleteAll
  '  ws.Protect

End Sub