Closing ActiveWorkbook but not ThisWorkbook

MistakesWereMade

Board Regular
Joined
May 22, 2019
Messages
103
My program opens a spreadsheet. I want to close this one using the x symbol without asking me if I'd like to close all other spreadsheets.

I have this written in my ThisWorkbook coding section.

Code:
Public swb As String


Private Sub Workbook_Open()


    Application.ScreenUpdating = False
    swb = ThisWorkbook.Name
    ThisWorkbook.Application.Visible = False
    Application.ScreenUpdating = True
    
    SplashUserForm.Show


End Sub


Private Sub WbkClose()
 
If swb <> ActiveWorkbook.Name Then
    ActiveWorkbook.Close = True
    ThisWorkbook.Close = False
End If


End Sub

I want the workbook associated with swb to remain open and not asked to be canceled. I have a separate button in my userform that is used to close this workbook.
 
I solved it! Been trying to solve such a seemingly simple issue for a while now. Thought I would post back here in case anyone is ever looking for a solution...

Below is my code in the ThisWorkbook section of my vba scripts.

Code:
Private BooleanForClosing As Boolean
Public swb As String


Private Sub Workbook_Open()


    Application.ScreenUpdating = False
    swb = ThisWorkbook.Name
    ThisWorkbook.Application.Visible = False
    Application.ScreenUpdating = True
    
    SplashUserForm.Show


End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)


If swb <> ActiveWorkbook.Name Then
    ActiveWorkbook.Close savechanges:=False
    Application.Visible = False
    UserForm1.Show vbModeless
    If swb = ActiveWorkbook.Name Then
        Cancel = True
        Exit Sub
    End If
End If



End Sub

Cheers
 
Last edited:
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Forum statistics

Threads
1,223,909
Messages
6,175,312
Members
452,634
Latest member
cpostell

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