Expert Needed: Prevent user from closing a Workbook

realniceguy5000

realniceguy5000

Board Regular
Joined
Aug 19, 2008
Messages
148
Hi,
I have a problem insofar as I have a workbook that is opened by a User which in turns opens another workbook.

When the user finishs his/her work and closes this workbook it in turns closes the second workbook.

The problem is when and if the user would try to close the second workbook before the first workbook. so What I want to do is prevent the second workbook from being closed if the first one is open.

I posted this on another site as well, a week or so ago, But thought I would give a shout out to the experts in here to see if someone knew of a way to make this work?


Here is the post to the other site
http://www.excelforum.com/excel-pro...losing-of-one-workbook-when-two-are-open.html

Thanks for any advice or suggestions.

Mike
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
In the ThisWorkbook module of the second workbook put the following code:
Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    'Prevent closure of this workbook if 'Book1 Test.xls' is open
    
    Dim i As Integer
    
    i = 1
    While i < Workbooks.Count
        If Workbooks(i).Name = "Book1 Test.xls" Then Cancel = True
        i = i + 1
    Wend
        
End Sub
Change "Book1 Test.xls" to the filename of your first workbook.
 
Upvote 0
If you are closing workbook B from the Workbook_BeforeClose event of workbook A then why not temporarly disable the application events as follows :

Code in Workbook A :

Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
    On Error Resume Next
    
    With Application
        If Not .Workbooks("B.xls") Is Nothing Then
            .EnableEvents = False
            .Workbooks("B.xls").Close True
            .EnableEvents = True
        End If
    End With
 
End Sub

Code in Workbook B :

Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
    On Error Resume Next
    
    If Not Workbooks("A.xls") Is Nothing Then
         MsgBox "You Must Click the Sort Button"
         Cancel = True
    End If
    
End Sub

Regards.
 
Upvote 0
Jaafar Tribak said:
If you are closing workbook B from the Workbook_BeforeClose event of workbook A then why not temporarly disable the application events as follows :

Code in Workbook A :

Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
    On Error Resume Next
 
    With Application
        If Not .Workbooks("B.xls") Is Nothing Then
            .EnableEvents = False
            .Workbooks("B.xls").Close True
            .EnableEvents = True
        End If
    End With
 
End Sub

Code in Workbook B :

Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
    On Error Resume Next
 
    If Not Workbooks("A.xls") Is Nothing Then
         MsgBox "You Must Click the Sort Button"
         Cancel = True
    End If
 
End Sub

Regards.
Click to expand...

Hi, Thanks for the help...

This script works if the user is only closing the window. However if they try to exit excel from WB(B)then workbook is closed.

However:

I was able to use Leith Ross attempt to solve the issue. You can see the results here if anyone has the same type of problem.
http://www.excelforum.com/excel-pro...losing-of-one-workbook-when-two-are-open.html

Many Thanks for the help.

Mike
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Macro Does Not Close Excel Workbook
2
Replies
14
Views
267
Jeffrey Mahoney
Jeffrey Mahoney
K0st4din
  • Question
Close workbook when changing ip or mas address
Replies
5
Views
144
Logit
Logit
S
  • Solved
Need macro to only close workbook at the end if it wasn't already open when Workbook Open Event runs
Replies
5
Views
223
sspatriots
S
K
  • Solved
Open 2 worksheets; closing first spreadsheet
Replies
8
Views
173
keycalinc
K
C
  • Question
Close window(abc20231231.xls) using vba
Replies
3
Views
227
RoryA
RoryA

Forum statistics

Threads
1,215,659
Messages
6,126,071
Members
449,286
Latest member
Lantern

Share this page

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
Back
Top