Solved: How to correct hide sheets when more than one workbook is closed?

[jiura]

Hello to all!
I want my workbook hide three sheets when it closed. I qrite such code:


Application.ActiveWorkbook.Unprotect Password:=SheetPaswordPr
Sheets("1").Visible = False
Sheets("2").Visible = False
Sheets("3").Visible = False
Application.ActiveWorkbook.Protect Password:=SheetPaswordPr


All works right when one Excel file is open. But when there are two excel files (my file and one more). And my file is not Active. THen If user clicks on cross witch close all excel workbooks, excel shows an error: "Subscribt out of range". How I must correct my code?

[Bob Phillips]

Try preceding Sheets With Thisworkbook, that is Thisworkbook.Sheets.

[jiura]

That's doesn't work: excel shows error message 1004 that "Could't set visible property worksheet class" (translation is not exact, becouse this message I receive On russian)

[Bob Phillips]

Perhaps it isn't called ThisWorkbook in Russian?

[jiura]

No, The VBA captions is the same. Does it work on your computer?
Code I post above must be posted in event:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

I repeat:
1. OPen two files - first with this code and second any other (it may be new clear file).
3. Focus on clear new file
4. Tried to close them all: by clicked on the top-right cross of the window.
Tht's is importent, ti click on cross witch close then all.

[Bob Phillips]

I don't have sheets names 1,2,3, mine are Sheet1, Sheet2, Sheet3 and so on so this works



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ThisWorkbook.Unprotect Password:=SheetPaswordPr
ThisWorkbook.Sheets("Sheet1").Visible = False
ThisWorkbook.Sheets("Sheet2").Visible = False
ThisWorkbook.Sheets("Sheet3").Visible = False
Application.ThisWorkbook.Protect Password:=SheetPaswordPr
End Sub


Note also that you cannot hide all the sheets at least one must be visible.

[jiura]

Hm, that's strange, may be I did something wrong before but now all is working! Thank you