VBA Close Excel after MsgBox Ok is pressed

LNG2013

Active Member
Joined
May 23, 2011
Messages
465
I am trying to close out of Excel after the Msgbox ok is pressed.

The msg only appears if there is no data. After which I want excel to close out completely.

VBA Code:
Sub AStart()

'

Application.ScreenUpdating = False ' Added to speed up the process
Sheets("Data").Cells.Clear ' Clear Data WS in case data was accidnetlally left over.

    Workbooks.Open Filename:="C:\temp\ReportOutput.xls" ' Opens Access exported Excel file
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy ' Select and copy all the data from ReportOutput.xls
    Windows("Report.xlsm").Activate
    Sheets("Data").Select
    Range("A1").Select
    ActiveSheet.Paste    ' Activate and Paste data back over to
    Application.CutCopyMode = False
    'Sheets.Add Before:=ActiveSheet
  
  
 ' This code is being placed so if there is no data to exit any further code and let user no there is no data between that range.
[B]
If Len(Range("A2").Value) = 0 Then
  MsgBox "HELLO!. "
    
      Windows("ReportOutput.xls").Activate
                 ActiveWorkbook.Close
        Windows("Report.xlsm").Activate
                ActiveWorkbook.Close
                SaveChanges = False
                    Application.Quit[/B]

 Exit Sub

End If

    Sheets("PivotTable").Select
    Application.DisplayAlerts = True
    Set PSheet = Worksheets("PivotTable")
    Set DSheet = Worksheets("Data")
    'Define Data Range
    lastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    Set PRange = DSheet.Cells(1, 1).Resize(lastRow, LastCol)
    'Define Pivot Cache
    Set PCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange). _
    CreatePivotTable(TableDestination:=PSheet.Cells(5, 1), _
    TableName:="PivotTable2")
    

Call AMasterBuild2

End Sub
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,615
Office Version
  1. 365
Platform
  1. Windows
So what's the issue? You get a prompt to save ReportOutput.xls? Then save that workbook, same as the next one.
Some other problem?
AFAIK, when you invoke Quit, unfinished code will continue to run (in certain circumstances, it does in Access vba). So if you get the message box you invoke quit but then you Exit the sub. Not sure what effect that will have. Since you want to quit, the exit shouldn't be necessary. I'd remove it to see what happens.
 
Upvote 0

Jambi46n2

Active Member
Joined
May 24, 2016
Messages
256
Office Version
  1. 365
Platform
  1. Windows
Something like this perhaps?

VBA Code:
ActiveWorkbook.Close SaveChanges:=False
 
Upvote 0

LNG2013

Active Member
Joined
May 23, 2011
Messages
465
Thank you both -

I figured it out :


VBA Code:
If Len(Range("A2").Value) = 0 Then
  MsgBox "HELLO!. "
   
      Windows("ReportOutput.xls").Activate
                 ActiveWorkbook.Close
        Windows("Report.xlsm").Activate
                ' ActiveWorkbook.Close  - Deleted this Can't exit the application if the workbook is quit
                ' SaveChanges = False  - Deleted this
                    Application.Quit

 Exit Sub
 
Last edited by a moderator:
Upvote 0

rlv01

Well-known Member
Joined
May 16, 2017
Messages
2,025
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Assuming you are not the user, do you really want to force Excel to quit? What if the user has another workbook open for something unrelated to your macro? Won't that upset your users?
 
Upvote 0

Forum statistics

Threads
1,186,400
Messages
5,957,635
Members
438,311
Latest member
llane23

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