VBA for Auto Closing with the Same Macro for all My Workbooks

[daveG777]

How can I automatically close all my current and future workbooks with the same VBA command? For example, I want all my workbooks to save while protecting all sheets.

I just want to set it up as a default and not have to enter it for each new workbook. However, it's OK if it works only on my computer.

 

[johnnyL]

Maybe something like:

Sub ProtectAllWorksheetsAndSaveAllOpenWorkbooks()
'
    Dim SheetsPassword  As String
    Dim WB              As Workbook
    Dim WS              As Worksheet

    SheetsPassword = "12345"                                             ' <--- Set this to the password to be used for sheet protection
'
    For Each WB In Application.Workbooks
        If Not WB.ReadOnly And Windows(WB.Name).Visible Then

            For Each WS In WB.Worksheets
                WS.Protect Password:=SheetsPassword
            Next
'
            WB.Save
        End If
    Next
End Sub

 

[daveG777]

Hmmm...I was hoping for something that worked like Auto_Close, which I think works if it's part of the VBA for a specific workbook.

In other words, I don't want to invoke it each time I close a spreadsheet or even to install it in each workbook. Instead, I'd like to install it just once, perhaps in Personal.xlsb, and to have it work automatically whenever a workbook or sheet closes on my computer. (I expect to have to install it once on each computer I work with.)

 

[johnnyL]

Sorry, I am not understanding your request.
1) Your first post mentions to close all workbooks and protect all worksheets. Your last post mentions closing just one workbook.
2) Your first post mentions just working on your computer. Your last post mentions working on each computer you work with.

 

[daveG777]

Sorry, I am not understanding your request.
1) Your first post mentions to close all workbooks and protect all worksheets. Your last post mentions closing just one workbook.
2) Your first post mentions just working on your computer. Your last post mentions working on each computer you work with.

In my first post, I was hoping for an approach that would apply to all my workbooks, present or future.

However, after hearing what people had to say and after reading elsewhere, I gave up on the first approach. As I explained, I'll just use a template for all my future workbooks, which would automatically include the Auto_Close command.