unexpected debugging issue. Worked fine for ages.

[MarkBeck]

Hi all

Here is my code

Sub HideSheets()

For Each sh In Sheets
If sh.Name <> "Welcome" Then [COLOR=red yellow]sh.Visible=xlVeryHidden[/color]
Next sh
Sheets("Welcome").Range("a1").Select
End Sub

This worked fine for ages. Suddenly there is this debugger kicking in.

Can anyone spot why?

Mark

 

[SkipVought]

Hi,

ALL sheets cannot be hidden.

Sub HideSheets()
    
    With Sheets("Welcome")
       .Visible = xlSheetVisible
       .[A1].Select
    End With
    For Each sh In Sheets
        If sh.Name <> "Welcome" Then sh.Visible=xlSheetVeryHidden
    Next sh
End Sub

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MarkBeck]

Hi Skip.

I added a statement "Sheets("welcome").Select", to get me there first.

Unfortunately the debugger still kicks in a the same spot.

I should point out that this code worked well for 2 years! I cant for the life of me think of what would have changed.

Mark

 

[SkipVought]

Do you have multiple workbooks open?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

...maybe your PERSONAL.XLS, which is hidden BTW?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MarkBeck]

>Multiple

Tried with and without. No result.


>Personal.xls

Recorded a simple macro to the Personal file. Its exists in the VBA. Again, no result.

Thanks for your efforts Skip.

Mark

 

[Andrzejek]

Just a guess here....

How is [tt]sh[/tt] defined as?

Have fun.

---- Andy

 

[SkipVought]

Sub HideSheets()
  [red][b]With thisworkbook[/b][/red] 
    With [red][b].[/b][/red]Sheets("Welcome")
       .Visible = xlSheetVisible  [red][b]'notice the .Sheets[/b][/red]
       .[A1].Select
    End With
    For Each sh In [red][b].[/b][/red]Sheets  [red][b]'notice the .Sheets[/b][/red]
        If sh.Name <> "Welcome" Then sh.Visible=xlSheetVeryHidden
    Next sh[red][b]
  end with[/b][/red]
End Sub

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MarkBeck]

I dont know. I found no declaration for it. I assumed that its a VBA shortcut for 'sheet'.

Besides. If that would have been the problem, the debugger would have kicked in at any of;

For Each sh In Sheets
If sh.Name <> "Welcome

 

[Andrzejek]

That's probably not an issue here, but it is good practice to have [tt]Option Explicit[/tt] at the top of the code.

Have fun.

---- Andy

 

[MarkBeck]

Skip.

Copied and pasted your code.

Same result.

I tried xlsheetHidden, VeryHidden. No result.

 

[PHV]

Which error message ?
Which line of code highlighted ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TonyJollans]

I haven't seen this for a while but there have been reports of it on and off for a couple of years and I have experienced it myself more than once. I would love to know what causes it, but I have never seen a solution beyond rebooting (or maybe just closing and restarting all Office apps) and it has a tendency to return. There is nothing particular about your code (that I am aware of) that causes it - the cause is somewhere else (presumably some sort of corruption but, of what, I don't know).

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[MarkBeck]

PHV

Runtime error 1004

Unable to set the Visible property of the Worksheet class.

 

[MarkBeck]

Tried rebooting twice already.

 

[TonyJollans]

Oh - Ignore my post! I read your question as the debugger kicking in just because it felt like it, not because you had an actual error.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[MarkBeck]

OK.

It seems a user sheet I had added, somehow contrived to cause all this. I have deleted the sheet and redid it.

All is now fine.

For the sake of clarity, here is the only other piece of code that could have somehow affected this (perhaps by not closing properly last time around)

Sub ProtectSheets()
Application.ScreenUpdating = False
ShowSheets
Sheets("LVB").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ‘ added sheet
Sheets("HZ").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("AJ").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("WL").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("NN").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("SW").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("HD").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
End Sub

 

[combo]

For testing, after error debug the code, display 'Immediate' window and here:

? sh.name

or open the 'Locals' window and see what 'sh' is. Check what sheets are visible in excel.

combo