Results 1 to 4 of 4

Thread: Unload/Unhide Sheet Based on Name
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2016
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Unload/Unhide Sheet Based on Name

    Hi, I am trying to unhide a very hidden worksheet based on a name picked from the drop down list. I am getting a Run-time error 91 Object variable or with block variable not set on the "If ws = Worksheets("Administrator").Range("B9").Value Then" line. Any ideas on how to fix this?

    Code:

    Dim ws As Worksheet

    If ws = Worksheets("Admin").Range("B9").Value Then
    ws.Visible = True
    End If

    Call Macro1


    End Sub

    Thanks for the help.

    Mike

  2. #2
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    903
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Unload/Unhide Sheet Based on Name

    try this:
    Code:
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    
    
    If ws.Name = Worksheets("Sheet1").Range("B9").Value Then
    ws.Visible = True
    Call Macro1
    Exit For
    End If
    Next ws
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  3. #3
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,392
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Unload/Unhide Sheet Based on Name

    You could just use this single line which will just do nothing if the tab is visible:

    Code:
    Sheets(CStr(Worksheets("Admin").Range("B9"))).Visible = xlSheetVisible
    Robert
    Last edited by Trebor76; Sep 24th, 2018 at 07:15 PM.

  4. #4
    New Member
    Join Date
    Dec 2016
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Re: Unload/Unhide Sheet Based on Name

    Thank you both. The code worked perfectly.



    Quote Originally Posted by manthony View Post
    Hi, I am trying to unhide a very hidden worksheet based on a name picked from the drop down list. I am getting a Run-time error 91 Object variable or with block variable not set on the "If ws = Worksheets("Administrator").Range("B9").Value Then" line. Any ideas on how to fix this?

    Code:

    Dim ws As Worksheet

    If ws = Worksheets("Admin").Range("B9").Value Then
    ws.Visible = True
    End If

    Call Macro1


    End Sub

    Thanks for the help.

    Mike

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •