VBA-enabled Excel 2010 workbook will not close when any other workbook is open

1
votes

I have recently finished an Excel workbook that utilizes a fair amount of VBA. The spreadsheet is always able to perform all of its intended functions, and there are no issues when it is the only workbook open in Excel. However, when any other workbook is open (unrelated files included), the workbook will not close. Clicking on the red "x" in the top right corner simply does not do anything, and no error messages are shown. In order to close it, all other Excel windows must be closed beforehand.

While this does not detract from the functionality of the workbook itself, enough people will be using it that I want to avoid bringing about the inconvenience of having to close any other workbooks they have open before being able to close this one.

So, does anyone have any ideas as to what is causing this to happen? I do have code for the Workbook_Close() event, but this is designated as private and only located within the ThisWorkbook part of this spreadsheet. I'm confused as to why another sheet with no VBA involved prevents this one from closing. Please let me know if there is anything I can clarify, thank you.

EDIT - Here is the code for the Workbook_BeforeClose() event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Me.Saved = False Then Me.Save
Call PrepareWorkbook

End Sub

Sub PrepareWorkbook()

Application.ScreenUpdating = False
Call UnprotectWorkbook

Dim masterSheet As Worksheet
Set masterSheet = ActiveWorkbook.Worksheets("PLANNING")

For Each sheet In ActiveWorkbook.Worksheets
    If Not (sheet.Name = "README" Or sheet.Name = "PLANNING" Or sheet.Name = "PREV") Then
        sheet.Visible = xlSheetVeryHidden
    End If
    If (sheet.Name = "README" Or sheet.Name = "PLANNING" Or sheet.Name = "PREV" Or sheet.Name = "ADMIN") Then
        Call ProtectSheet(ActiveWorkbook.Worksheets(sheet.Name))
    Else
        Call ProtectPlannerSheet(ActiveWorkbook.Worksheets(sheet.Name))
    End If
Next

If ActiveWorkbook.ActiveSheet.Name <> "PLANNING" Then
    masterSheet.Select
End If

Call ProtectWorkbook

Application.ScreenUpdating = True

End Sub

I should note that commenting out the call to PrepareWorkbook() within the Workbook_BeforeClose() event did not solve the problem.

2
excelexcel-2010vba
What code do you have in the Workbook_BeforeClose event? – Rory
@Rory I posted the code - is there a separate event for BeforeClose, or is this the same thing as close? – Shoeheart
There is no Workbook_Close event in Excel. I assumed that was a typo on your part. The code you have shouldn't ever run unless it's called by some other code. – Rory
Thanks, I fixed that issue, but my original problem still stands. – Shoeheart

2 Answers

0
votes

Sounds like your are doing something to that Workbook like "editing a cell" which is preventing it from closing (other things like having the formatting popup open will also do this).

Trying selecting the workbook, pressing ESC, and then manually closing it. If that works then look for the place in your code where you are "editing a cell" or something similar.

0
votes

I've found the source of my problem, which lies within how I'm protecting the workbook. In my VBA code, I set the "Windows" property of the Workbook.Protect function to True; I'm not sure if this is Excel's intended functionality or not, but regardless, this made it so that the current workbook could not be closed if any other workbook was also open. Changing this property to False fixed the problem, and I can close the workbook as would normally be expected.

So, I would suggest to avoid ever setting the "Windows" property to True unless absolutely necessary and you have a complete grasp of its actual function.