Workbook close error

  • Thread starter Thread starter Rich
  • Start date Start date
R

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
 
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
 
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
 
This will err if Welcome is the last sheet. (But it probably isn't :-)

Best wishes Harald
 
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
 
Yes it was already visible, but I don't recall seeing anything from the OP
that it wouldn't be.
 
OP said "What is the problem with what I have typed?"; we should consider
everything and assume nothing.

HTH. Best wishes Harald
 
Back
Top