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
-----------------------------------------

What is the problem with what I have typed? Is there a better way of
hiding a range of sheets that are not called Welcome?
All comments are welcomed (as is any replacement code!)

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

"Rich" skrev i melding
m...
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
-----------------------------------------

What is the problem with what I have typed? Is there a better way of
hiding a range of sheets that are not called Welcome?
All comments are welcomed (as is any replacement code!)

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

--

HTH

RP

"Rich" wrote in message
m...
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
-----------------------------------------

What is the problem with what I have typed? Is there a better way of
hiding a range of sheets that are not called Welcome?
All comments are welcomed (as is any replacement code!)

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

Best wishes Harald

"Bob Phillips" skrev i melding
...

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

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

--

HTH

RP

"Harald Staff" wrote in message
...
This will err if Welcome is the last sheet. (But it probably isn't :-)

Best wishes Harald

"Bob Phillips" skrev i melding
...

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

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" skrev i melding
...
It didn't for me Harald, I tested with Welcome as the last sheet.

--

HTH

RP

"Harald Staff" wrote in message
...
This will err if Welcome is the last sheet. (But it probably isn't :-)

Best wishes Harald

"Bob Phillips" skrev i melding
...

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

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

--

HTH

RP

"Harald Staff" wrote in message
...
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" skrev i melding
...
It didn't for me Harald, I tested with Welcome as the last sheet.

--

HTH

RP

"Harald Staff" wrote in message
...
This will err if Welcome is the last sheet. (But it probably isn't :-)

Best wishes Harald

"Bob Phillips" skrev i melding
...

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

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

HTH. Best wishes Harald

"Bob Phillips" skrev i melding
...
Yes it was already visible, but I don't recall seeing anything from the OP
that it wouldn't be.

--

HTH

RP

"Harald Staff" wrote in message
...
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.