Workbook close error

[Rich]

I have the following code, that produces an error 400 when the macro
sub routine is run by itself. What I want it to do, is out a number of
sheets in a workbook, when the workbook closes, all sheets will be
veryhidden except one called "Welcome". As you can probably tell, I am
trying to make use of the 'force macros' style of hiding important
sheets before macros are enabled. If macros are disabled, then the
only sheet visible will "Welcome"

-----------------------------------------
Sub workbook_before_close()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Visible = xlVeryHidden
Next ws
Call Show_Welcome
Application.SaveWorkspace
End Sub

 

[Harald Staff]

It's a distinct limitation for xls files: At least one sheet must be
visible. So you can't hide all and then show one. Modify to something like

ThisWorkbook.Sheets("Welcome").visible = true
For Each ws In ThisWorkbook.Worksheets
if ws.name <> "Welcome" then ws.Visible = xlVeryHidden
Next ws

Note also that the workbook must be unprotected before you change sheet
visibility.

HTH. best wishes Harald

 

[Bob Phillips]

Hi Rich,

You cannot have all sheets hidden. so your code bombs on trying to hide the
last one, before the call to show the Welcome sheet

Try this amendment

Sub workbook_before_close()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Visible = (ws.Name <> "Welcome") * -1 - 1
Next ws
Application.SaveWorkspace
End Sub

 

[Harald Staff]

This will err if Welcome is the last sheet. (But it probably isn't

Best wishes Harald

 

[Bob Phillips]

It didn't for me Harald, I tested with Welcome as the last sheet.

 

[Harald Staff]

Then it was either not last from the collection point of view, or already
visible, or you have a visible chart sheet or similar besides the
worksheets. "You need macros enabled" sheets are often hidden after the code
unhid the others.

Sub test()
Dim S As String, WS As Worksheet
S = Worksheets(Worksheets.Count).Name
Worksheets(S).Visible = xlVeryHidden
For Each WS In ThisWorkbook.Worksheets
WS.Visible = (WS.Name <> S) * -1 - 1
Next WS
End Sub

But as stated; probably no big deal.

Best wishes Harald

 

[Bob Phillips]

Yes it was already visible, but I don't recall seeing anything from the OP
that it wouldn't be.

 

[Harald Staff]

OP said "What is the problem with what I have typed?"; we should consider
everything and assume nothing.

HTH. Best wishes Harald