CODEWITHSUNDEEP
excelvba
Gisse
Gisse

Reputation: 49

VBA runs code twice before close private sub

have following problem: My before close sub gives me an error in the Sheets("Country Selection").Visible = xlSheetVisible, even though it actually deletes the sheet through the loop. It seems like it pulls the code twice. Any clue why this is the case?

Private Sub workbook_BeforeClose(cancel As Boolean)

Dim ws As Worksheet
Application.DisplayAlerts = False

Sheets("Country Selection").Visible = xlSheetVisible

For Each ws In ThisWorkbook.Worksheets
    If ws.Visible = xlSheetVisible Then
        If ws.Name <> "Choose BU" Then ws.Delete
    End If
Next ws

Application.DisplayAlerts = True

ActiveWorkbook.Close SaveChanges:=True

End Sub

Upvotes: 1

Views: 743

Answers (1)

Plutian
Plutian

Reputation: 2309

The issue is your logic flow here. The BeforeClose sub will run when the closing sequence of the workbook is initiated. If you do so manually (by closing it form the menu or the X button) then this sub will run.

However you also have:

ActiveWorkbook.Close SaveChanges:=True

Which is redundant. The workbook is already being closed, and will automatically close after the sub is ran. This line will initiate another closing sequence, and run the sub again. You should replace it by:

Activeworkbook.save

so your changes are saved. It will then automatically close at the end of the "before close" sub.

Also a tip for you, I suggest using Thisworkbook.Save instead of Activeworkbook as it is more reliable.

Upvotes: 6

Related Questions