Type mismatch?

[Guest]

When I run this code, I get an error="Type mismatch." How come?

Public Sub HideSelectedSheets()
Dim Wsh As Worksheet
For Each Wsh In ActiveWorkbook.Sheets
If UCase(Wsh.Range("G1").Value) = "HIDE" Then
Wsh.Visible = xlSheetHidden
End If
Next
End Sub

Thank you.

John Wirt

 

[Bob Phillips]

John,

Try this

Public Sub HideSelectedSheets()
Dim Wsh As Worksheet
For Each Wsh In ActiveWorkbook.Worksheets
If UCase(Wsh.Range("G1").Value) = "HIDE" Then
Wsh.Visible = xlSheetHidden
End If
Next
End Sub

If it works, it means that you have another type of sheet, such as a chart,
and as you defined Wsh as a worksheet, it mis-matches. You can safely ignore
these, as they won't have a cell on anyway.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ken Macksey]

Hi

Don't know. It worked ok for me in excel 2002 unless I tried to hide all
sheets. One sheet must remain unhidden.

Ken

 

[Dave Peterson]

What's in G1 of the worksheet that the code blows up on?

If it's an error, then that could be the cause.

If UCase(Wsh.Range("G1").Text) = "HIDE" Then

Is one way to avoid it.

 

[Dave Peterson]

And if you have chartsheets (or other non-worksheets), you'd want to use:

For Each Wsh In ActiveWorkbook.Worksheets

 

[Bob Phillips]

As I said in my response

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

Yes, you did!

As I said in my response

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[John Wirt]

Warring MVPs. Bad news.

Yes, changing to "Worksheets" was the problem. I guess the odds of success
are high if two MVPS agree on the solution.

Thank you both.

John Wirt