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

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,032
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:
VBA Code:
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
VBA Code:
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?
VBA Code:
Call CostingDeleteAll
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,032
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Well, thanks for trying. I have had my pm privileges removed by the admins as I used to send the admins messages, asking for help with questions. They didn't like that, said it was against the rules.

I sent one once to an admin, they warned me not to do it again or I would have my privileges to send private messages removed. I have a disability which causes me to have a terrible memory so I did it again. I was told that I would not be able to send pms anymore. I tried to explain my situation but I was told that the same rule must apply to everyone so no compassion was shown and my privileges were removed.

I thought I would still be able to get pms and thought it would be good to stockpile all my useful threads in my inbox but they didn't only stop my ability to send pms, but also my ability to receive them too.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,032
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Jason, could you please try to send me a PM again with the url of this thead in it?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,851
Messages
5,598,452
Members
414,240
Latest member
xnanx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top