Issue on Close Q

[Sean]

I have the following code that, on close, it hides all the Sheets
except Splash. Problem is I'm getting a debug on -
ws.Visible = xlVeryHidden

It says - Unable to set the visible property of the worksheet class

I've all sheets protected and running 2003



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh As Worksheet
For Each ws In Worksheets
If ws.Name = "Splash" Then
ws.Visible = True
Else
ws.Visible = xlVeryHidden
End If
Next

End Sub

 

[Mike]

add the top line

On Error Resume Next
Dim sh As Worksheet
For Each sh In Worksheets

If sh.Name = "splash" Then
sh.Visible = True
Else
sh.Visible = xlVeryHidden
End If
Next

 

[Chip Pearson]

First, you haven't declared the variable 'ws'. Putting "Option Explicit" as
the very first line in the code module will require to explicitly declare
your variable. Do this, always.

You code will blow up if you don't have a sheet named "Splash" (test the
sheet in case it contains leading/trailing spaces, a misspelled name, etc).
If there is no sheet named "Splash" your code will attempt to set
xlVeryHidden on every sheet. A workbook must always have at least one
visible sheet. You'll blow up on the last sheet.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Dave Peterson]

And since one sheet must be visible, you might as make sure it's Splash:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet 'not worksheets
me.worksheets("splash").visible = xlsheetvisible
For Each ws In me.Worksheets
If lcase(ws.Name) = lcase("Splash") Then
'do nothing, already taken care of
Else
ws.Visible = xlVeryHidden
End If
Next ws
End Sub

Remember that the workbook still has to be saved for this to work
effectively--if the user closes without saving, all your work is to naught.

 

[Sean]

Thanks Guys, Fixed

And since one sheet must be visible, you might as make sure it's Splash:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet 'not worksheets
me.worksheets("splash").visible = xlsheetvisible
For Each ws In me.Worksheets
If lcase(ws.Name) = lcase("Splash") Then
'do nothing, already taken care of
Else
ws.Visible = xlVeryHidden
End If
Next ws
End Sub

Remember that the workbook still has to be saved for this to work
effectively--if the user closes without saving, all your work is to naught.

 

[Chip Pearson]

The only change I would make to Dave's otherwise fine code is to change

If lcase(ws.Name) = lcase("Splash") Then
to
If StrComp(ws.Name, "Splash", vbTextCompare) = 0 Then

StrComp is a much more efficient way to compare strings than using the "="
operator, especially if you have to convert the two strings to upper or
lower case for the compare. It won't make a visible difference in the posted
code, but it is a good habit to get into.

A quick and dirty test using GetTickCount and looping 10 million times
showed StrComp is about 10 times faster than using "=" with LCase.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

Thanks Guys, Fixed