Opening Hidden worksheets using VBA code

[tewellt]

I have a Excel 2013 workbook that I have created. I have a number of worksheets which I want to hide so that when a user opens the Workbook only the "Table of Content" shows. On this worksheet I have a set of buttons that should take the user to the right worksheet. If I do not hide the worksheet I can use the .select command and the user is taken to the correct worksheet.

Private Sub BTN_CERT_Click()
' Go to CERT Claims Dashboard sheet
  Sheets("CERT Claims Dashboard").Select
End Sub

If, however, I hide the worksheet I get a message Run-time error '1004' Select method of Worksheet class failed My question is how to I select and and make visible the worksheets. Then how to I hide the worksheet again when the use clicks on the TOC button to go back to the "Table of Contents" worksheet?

Thanks in advance for any help that you can offer.

 

[SQUIDD]

Hi


Try

Sheets("CERT Claims Dashboard"").Visible = True

and

Sheets("CERT Claims Dashboard"").Visible = false

Dave

 

[tewellt]

SQUIDD, Thanks for the reply. I think I have got the visible=True and False worked out on navigating from one worksheet to another. Now I have the same issue with code when I am attempting to save and then protect each worksheet. When I run the code below it throws the error Run-time error '1004' Select method of Worksheet class failed.

Private Sub BTN_Secure_Click()
Dim i As Integer, Tries As Integer
    Dim PassTry As String
    Const Pass As String = "Pass1"
    Const Pass2 As String = "Pass2"
     
    Tries = 3
    For i = 1 To Tries
        PassTry = InputBox("Please Enter A Password (Case Sensitive)", "Enter Password")
        Select Case PassTry
        Case Pass
            Worksheets("Table of Contents").Range("C23").Value = "Locked"    ' Displays workbook status on TOC worksheet
            GoTo PassCorrect
        Case Pass2
            Worksheets("Table of Contents").Range("C23").Value = "Locked"
            GoTo PassCorrect
        Case Else
        End Select
        If i < 3 Then If MsgBox(Tries - i & " Tries Remaining." & vbLf & vbLf & "Try Again?", vbYesNo) = vbNo Then Exit For
    Next i
    MsgBox "Access Denied"
    Exit Sub
PassCorrect:
    MsgBox "Password Correct, Access Approved"
    
    [COLOR=#FF0000]Sheets("Z2 Claims Dashboard").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True[/COLOR]

My question is basically can you perform any operation on a hidden worksheet?

 

[SQUIDD]

Hi

Sorry for the extremly late reply.
I am no expert but i believe im right in saying, you certianly cannot use the word select on hidden sheets.

If possible change your code to something like the following

With sheets("z2 clamis dashboard")
.do stuff
.do stuff
.do stuff
end with

I believe using with and end with will allow you to do things with hidden sheets.

Hope that makes sense

Dave