problem macro to show a warning sheet...

[Pierre via OfficeKB.com]

Hi,

I have the followign code .....

Private Sub Workbook_Open()
Dim Sheet As Worksheet
For Each Sheet In Worksheets
If Sheet.Name <> "waarschuwing" Then
Sheet.Visible = xlSheetHidden
End If
Next Sheet
Sheets("waarschuwing").Visible = xlSheetVeryHidden
Set Sheet = Nothing
end sub

When the code is run, i get an error message:

erro 1004
characteristic visible of class worksheet cannot be use

any ideas?
Pierre

 

[Guest]

Your code runs fine, the problem is you must have at least one visible sheet
in a file. Add a blank sheet first, then hide the rest. Exclude the token
visible sheet from your code.

HTH.

 

[Pierre via OfficeKB.com]

thanks quartz,

i adapted the code and that works but... now i have the following program
piece that does not work.

For Each Sheet In Worksheets
If Sheet.Name <> "waarschuwing" Or Sheet.Name <> "Blad1" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next Sheet

it gives an error message on the third line...
the message was not there before but when i put the Or Sheet.Name...
statement in, i got the error...

any suggestions ?
thanks,
Pierre

Your code runs fine, the problem is you must have at least one visible sheet
in a file. Add a blank sheet first, then hide the rest. Exclude the token
visible sheet from your code.

HTH.

Hi,

[quoted text clipped - 18 lines]

any ideas?
Pierre

 

[Guest]

Hi,
Better to use the sheets.Count and loop throught each sheet.

for i=1 to sheets.count
if sheets(i).name<>"blah" or sheets(i).name<> "Blergh" then
sheets(i).visible=xlveryhidden
end if
next

It also might be that you must xlhidden before you can xlveryhidden

see my stuff at
http://www.geocities.com/excelmarksway

thanks quartz,

i adapted the code and that works but... now i have the following program
piece that does not work.

For Each Sheet In Worksheets
If Sheet.Name <> "waarschuwing" Or Sheet.Name <> "Blad1" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next Sheet

it gives an error message on the third line...
the message was not there before but when i put the Or Sheet.Name...
statement in, i got the error...

any suggestions ?
thanks,
Pierre

Your code runs fine, the problem is you must have at least one visible sheet
in a file. Add a blank sheet first, then hide the rest. Exclude the token
visible sheet from your code.

HTH.

Hi,

[quoted text clipped - 18 lines]

any ideas?
Pierre

 

[Rowan Drummond]

Hi Pierre

I think you need to change the "Or" statement to an "And" otherwise you
will still be trying to hide every sheet:

For Each Sheet In Worksheets
If Sheet.Name <> "waarschuwing" And Sheet.Name <> "Blad1" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next Sheet

Hope this helps
Rowan

thanks quartz,

i adapted the code and that works but... now i have the following program
piece that does not work.

For Each Sheet In Worksheets
If Sheet.Name <> "waarschuwing" Or Sheet.Name <> "Blad1" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next Sheet

it gives an error message on the third line...
the message was not there before but when i put the Or Sheet.Name...
statement in, i got the error...

any suggestions ?
thanks,
Pierre

Your code runs fine, the problem is you must have at least one visible sheet
in a file. Add a blank sheet first, then hide the rest. Exclude the token
visible sheet from your code.

HTH.

Hi,

[quoted text clipped - 18 lines]

any ideas?
Pierre