Hide All Worksheets Except One

ola · Jun 20th 2010

Hello

Im trying to hide/unhide spesific sheets in my workbook.
The code is in a modul, and i want to start them in the end of a userform (homemaid logg inn) (like hide.start()).

The probleam is that i get som errors when i run the code.

Code

Sub start_click()
Dim ws As Worksheet
Set ws = Worksheets("INNLOGGING")


If ws.Cells(4, 8).value = "1" Then
Ark1.Visible = xlSheetVisible
Ark3.Visible = xlSheetVisible
Ark5.Visible = xlSheetVisible
Ark7.Visible = xlSheetVisible
Ark9.Visible = xlSheetVisible
Ark11.Visible = xlSheetVisible
Ark12.Visible = xlSheetVisible
Ark13.Visible = xlSheetVisible
Ark14.Visible = xlSheetVisible
Ark16.Visible = xlSheetVisible
Ark19.Visible = xlSheetVisible
Ark20.Visible = xlSheetVisible
Ark21.Visible = xlSheetVisible
Ark22.Visible = xlSheetVisible
Ark23.Visible = xlSheetVisible
Ark24.Visible = xlSheetVisible
Ark25.Visible = xlSheetVisible
Ark26.Visible = xlSheetVisible
Ark27.Visible = xlSheetVisible
Ark28.Visible = xlSheetVisible
Ark29.Visible = xlSheetVisible
Ark30.Visible = xlSheetVisible
Ark31.Visible = xlSheetVisible
Ark33.Visible = xlSheetVisible
Ark36.Visible = xlSheetVisible
Ark37.Visible = xlSheetVisible


ElseIf ws.Cells(4, 8).Value = "0" Then
Ark1.Visible = xlSheetHidden
Ark3.Visible = xlSheetHidden
Ark5.Visible = xlSheetHidden
Ark7.Visible = xlSheetHidden
Ark9.Visible = xlSheetHidden
Ark11.Visible = xlSheetHidden
Ark12.Visible = xlSheetHidden
Ark13.Visible = xlSheetHidden
Ark14.Visible = xlSheetHidden
Ark16.Visible = xlSheetHidden
Ark19.Visible = xlSheetHidden
Ark20.Visible = xlSheetHidden
Ark21.Visible = xlSheetHidden
Ark22.Visible = xlSheetHidden
Ark23.Visible = xlSheetHidden
Ark24.Visible = xlSheetHidden
Ark25.Visible = xlSheetHidden
Ark26.Visible = xlSheetHidden
Ark27.Visible = xlSheetHidden
Ark28.Visible = xlSheetHidden
Ark29.Visible = xlSheetHidden
Ark30.Visible = xlSheetHidden
Ark31.Visible = xlSheetHidden
Ark33.Visible = xlSheetHidden
Ark36.Visible = xlSheetHidden
Ark37.Visible = xlSheetHidden
End If


End Sub

Display More

As you can see in the code I have a full functional logg inn method, that gives 1 if its correct, and 0 if its not.

The error i get is thet is: Method "Visible" of Object "Worksheet" failed.

Hope someone can help me!

**Reafidy** · Jun 20th 2010

Re: Hide/Undide Multiple Sheets In VBA Code

Please read the rules regarding thread titles. Yours has been changed this time.

Try:

Code

Option Explicit
Sub HTH()
    Dim wsLoop As Worksheet
    Dim vVisible As Variant
    With Worksheets("INNLOGGING").Cells(4, 8)
        If .Value = "1" Then
            vVisible = xlSheetVisible
        ElseIf .Value = "0" Then
            vVisible = xlSheetHidden
        End If
    End With
 
    For Each wsLoop In Sheets(Array("Ark1", "Ark3", "Ark5"))
        wsLoop.Visible = vVisible
    Next wsLoop
End Sub

Display More

**Reafidy** · Jun 20th 2010

Re: Hide/Undide Multiple Sheets In VBA Code

If you are running all all sheets except for 1 you will be better to use something like:

Code

For Each wsLoop In ThisWorkbook.Worksheets
        If wsLoop.Name <> "SheetX" Then wsLoop.Visible = vVisible
    Next wsLoop

ola · Jun 20th 2010

Re: Hide All Worksheets Expect One

Thank you for your help!

I found out a way to solve my probleam!

jhenderson · Jun 20th 2010

Re: Hide All Worksheets Except One

OLA
Please let us know how you solved your problem, there could be others with the same problem
thank you

ola · Jun 20th 2010

Re: Hide All Worksheets Except One

I found out that I had password protected the structur of the workbook.
When I removed the password, it worked.

**Reafidy** · Jun 20th 2010

Re: Hide All Worksheets Except One

Thanks for posting your solution.

If you are looking for a way to encourage users to enable macros by hiding worksheets then have a look at: http://www.ozgrid.com/forum/sh…89037&p=430017#post430017

Hide All Worksheets Except One

Participate now!

Share