Macro needed to close only active Workbook

[chamdan]

Hi Hans,

The objective is to save close and Quit Excel only if one workbook is opened.
If more than one workbook is opened then Save and Close the active one and exit only.

Regards,

Chuck

[HansV]

Where do you want to store this macro? In your personal macro workbook Personal.xlsb, in an Excel add-in or in a specific workbook?

[chamdan]

In the active workbook.

Chuck

[Rudi]

What do you mean by exit only?
One WB; save, close and exit Excel (understood!)
More than one WB; save and close the active one (understood!) - but exit what???

[HansV]

Perhaps this? It also takes Personal.xlsb into account:

Code: Select all

Sub CloseMe()
    If Workbooks.Count = 1 Or Workbooks.Count = 2 And _
            (LCase(Workbooks(1).Name) = "personal.xlsb" Or _
            LCase(Workbooks(2).Name) = "personal.xlsb") Then
        ActiveWorkbook.Save
        Application.Quit
    Else
        ActiveWorkbook.Close SaveChanges:=True
    End If
End Sub

[chamdan]

Sorry Hans for not being clear.

I may have multiple workbooks opened while working, I want for one particular workbook when run, if the user select the button to Save & Close then the macro will only save and close this particular workbook and close it but Excel will not close so the other opened workbooks will not be affected.
Now if only this particular workbook is openned then when the user click the button then it will save, Close and quit Excel.

Hope this is clear.

Regards,

Chuck

Below is and example of waht I wrote but crashed my connection to the network.

Code: Select all

Public Sub SaveActiveWbOnly()
Dim AwB As String
AwB = ActiveWorkbook.Name
Dim Wb As Workbook
For Each Wb In Workbooks
    If Workbooks.Count < 2 Then
        Application.DisplayAlerts = False
        Wb.Save
        Wb.Close
        Application.StatusBar = "Active.Wb Saved."
        Application.DisplayAlerts = True
        GoTo QuitProcess
    End If
    If ActiveWorkbook.Name = AwB Then
        Wb.Save
        Wb.Close
    Else
        Wb.Save
    End If
    Application.StatusBar = Wb.Name & " Workbook has been Saved."

Next Wb
Exit Sub
QuitProcess:
    Application.StatusBar = ""
    Application.Quit
End Sub

[HansV]

Have you tried the macro that I posted?

[chamdan]

Snap1.jpg

This is what it is giving me.

Chuck

[HansV]

Sorry, try this version:

Code: Select all

Sub CloseMe()
    If Workbooks.Count = 1 Then
        ActiveWorkbook.Save
        Application.Quit
    ElseIf Workbooks.Count = 2 And _
            (LCase(Workbooks(1).Name) = "personal.xlsb" Or _
            LCase(Workbooks(2).Name) = "personal.xlsb") Then
        ActiveWorkbook.Save
        Application.Quit
    Else
        ActiveWorkbook.Close SaveChanges:=True
    End If
End Sub

[chamdan]

It worked!

Thanks!

Chuck

P.S.: What does mean a variable that is declared outside of the Sub.
e.g.:

Code: Select all

Dim Ps As Integer
Subt Whatever()
End Sub

[HansV]

It means that the variable is available to all procedures (Subs) and functions within the same module. If you use the keyword Public, as in

Public Ps As Integer

the variable will be available to all procedures and subs in the same workbook. See A matter of scope: visibility of code elements.

[chamdan]