Avatar of Parkhill Mays
Parkhill MaysFlag for United States of America asked on

Error VB Code when running expand / hide code for worksheet tabs

Hi - I am trying to figure out why my VB Code is not running for expanding and hiding worksheet tabs; I have copied the code and replaced with the proper tab names.  Below is the code and it errors out on the first Show2 (name of the tab) IF phrase (highlighted below). thank you for any help


Private Sub Worksheet_Activate()
    Dim sheet As Worksheet

    Dim sheetsArray As Sheets
    Set sheetsArray = ThisWorkbook.Sheets(Array("Full", "NewBiz", "Renewals"))


    Application.ScreenUpdating = False
    If ShowHide1.Name = "Show2" Then

        For Each sheet In sheetsArray
            sheet.Visible = xlSheetVisible
        Next sheet

        ShowHide1.Name = "Hide"
        ShowHide1.Tab.Color = vbYellow

        Sheet81.Activate
    Else

        For Each sheet In sheetsArray
            If (sheet.Name <> ShowHide1.Name And sheet.Name <> AlwaysShow.Name) Then
               sheet.Visible = xlSheetVeryHidden
            End If
        Next sheet

        ShowHide1.Name = "Show2"
        ShowHide1.Tab.Color = vbGreen

        AlwaysShowNeverDeleteThis.Activate
    End If
    Application.ScreenUpdating = True
End Sub


Open in new window


VBAMicrosoft Excel

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
ste5an

There is no object variable ShowHide1 declared in your code sample. Probably thus erroring out (no reference).

1) Please edit your post and use the embed code button (</>) in the tool bar or explicit [code][/code] tags to increase readability.
2) Each VBA code modul must start with an Option Explicit to catch errors of undeclared variables at compile time.
ASKER
Parkhill Mays

Hi thank you for the comment - I don't quite follow the instructions you are giving to me.  Unfortunately I am not a developer so that may be part of the issue.  This is the line of code that keeps getting highlighted by the debugger

If ShowHide1.Name = "Show2" Then

My sheet name that this code is imbedded in is named Show2.  also, my other sheet names I am trying to hide are called 1. Full 2. New 3. Renewals and the additional sheet that the original example suggested I add is called AlwaysShowNeverDeleteThis

thanks for any help
ASKER
Parkhill Mays

Sending this request for help also to the Microsoft topic list
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
Parkhill Mays

Thank you very much for the help
Martin Liss

Under what circumstances do you want tabs hidden?

Can you attach your workbook? The easiest way to do that is to make sure the little TEXT/VISUAL slider in the upper right-hand corner of a comment is on the VISUAL side and then drag your workbook into the comment.
Norie

What is ShowHide1 supposed to refer to?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Parkhill Mays

Hi thank you - here is the file.  I also wonder if it has to do with the first tab which I created according to the instructions of the post which contained the sample code.  I am honestly not sure what that tab's functionality is in this equation.  thanks for taking a lookBook3.xlsx
Martin Liss

When should the hiding happen? In other words what action should cause it?
Martin Liss

And also tell me in general what you are trying to do.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
Parkhill Mays

Martin:
I am following a post from 2 years ago that explains how to use a worksheet tab to Hide and Unhide other worksheets.  My practical use is a very large financial model which has 80 worksheets.  Many (perhaps 60) are use as storage or reference only and don't need to remain visible all of the time.  However with that many worksheets, the traditional way of Format / Hide & Unhide sheets is not very efficient.

The post explains howVB code can be used within a particular worksheet tab whereby clicking it will hide and unhide a group of worksheets that are named in the code.  The author event sent an example worksheet which contained the exact code, and that worksheet example works great.  The problem is: I have to replace the worksheet names in thatVB code from the sample worksheet with the actual worksheet names in my files.

There is also this "trigger" worksheet the author references which has to remain visible.  Im not sure what its purpose is.  But essentially that is my task - to use a worksheet tab to initiate automated Hiding and Unhiding of other worksheets. It looks like I have an error only on one line of code and if I can somehow solve it it wouldreally help my model

thanks

Norie

Can you post a link to the post you got the code from?
ASKER
Parkhill Mays

This is one from this (Expert Exchange) forum, that I did not use but it covers the same issue:

https://www.experts-exchange.com/articles/1685/Excel-Using-a-Sheet-Tab-as-a-Button-for-Expanding-Collapsing-Supplementary-Sheets.html

This is the one I used from the Microsoft Community - it is the post from Jamil Mohammed and he provides a good example withhis attached excel file

https://techcommunity.microsoft.com/t5/excel/collapse-and-expanding-worksheet-tabs/m-p/170866


thank you


Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Martin Liss

In the attached workbook you'll find three sheets called "This", "That" and "Another One". From any sheet press Ctrl+Shift+H to hide those sheets and Ctrl+Shift+U to unhide them.
ASKER
Parkhill Mays

Martin I have a number of hide / unhides to create in the workbook I am working with.  this appears to be a regular macro which I think I could manage.  But I share this with other users and was hoping to get something more visual working.  If there is a way to get it done using a worksheet tab that would be preferable (using the other code if I can figure it out).  I thank you nonetheless and could use this as a Plan B if there isn't the other solution
Martin Liss

What do you mean by "get something more visual working"?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Martin Liss

How about this?
29224810a.xlsm
ASKER
Parkhill Mays

I think it could work, but it seems to fail on the Show button.  I get the debugger popping up?  this is the line that is highlighted

ActiveSheet.Shapes("ButtonHide").TextFrame.Characters.Text = "Hide Worksheets"
Martin Liss

I changed the macro to specifically refer to a sheet named Sheet1 instead of ActiveSheet.
29224810b.xlsm
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Parkhill Mays

thank you - I believe this will work well.  I need to weave it back into my model and referenced the worksheet names.  I think I can do that and may write back if it gives me trouble

thank you very much for helping me
Martin Liss

Your welcome. The attached workbook's code will make your life a lot easier as it hides/unhides based on the sheets' tab color.
29224810c.xlsm
Martin Liss

Oh and since this is your first question and you may not know how to close a question, please see the the "How do I mark a comment as a solution..." article on the second page of this Experts Exchange FAQ.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Parkhill Mays

Martin:
The last one you sent with the color coding seems to work great - except that I have no idea how to get that back into my financial model (where I need to use it).  I tried dragging the worksheet with the VB code in there to my model and it wont drag like a normal worksheet.  Any thoughts?  thank you and points noted on closing the question

Martin Liss

To color the tabs, press and hold the Ctrl key and then click on the tabs of the worksheets that are subject to being hidden. Then when you have them all selected,
  1. let go of Ctrl key
  2. right click on any one of the selected tabs
  3. choose "Tab Color"
  4. select the arrow at the right of "Tab Color"
  5. choose the yellow box under "Standard Colors"
You can choose a different color if you like but if you do you'll need to change vbYellow in the code to vbRed or vbGreen, etc.
You may also want to change line 7.
Do you know how to add a button like the one in my workbook and assign my macro to it?

Here is the code:
Sub HideSheets()
Dim ws As Worksheet
Dim wsMain As Worksheet

Application.ScreenUpdating = False

Set wsMain = Sheets("Sheet1")

With wsMain
    If .Shapes("ButtonHide").TextFrame.Characters.Text = "Hide Worksheets" Then
        For Each ws In ThisWorkbook.Worksheets
            If ws.Tab.Color = vbYellow Then
                ws.Visible = xlSheetVeryHidden
            End If
        Next
        .Shapes("ButtonHide").TextFrame.Characters.Text = "Show Worksheets"
    Else
        For Each ws In ThisWorkbook.Worksheets
            ws.Visible = xlSheetVisible
        Next
        .Shapes("ButtonHide").TextFrame.Characters.Text = "Hide Worksheets"
    End If
End With

Application.ScreenUpdating = True
End Sub

Open in new window


Here are instructions on how to install the macro.

  1. In Excel, Press Alt+F11 to open the Visual Basic Editor (VBE)
  2. Right-click on your workbook name in the "Project-VBAProject" pane. If you don’t see an existing module then select Insert -> Module from the menu bar. Otherwise just select the module.
  3. Copy the macro (you can use the ‘Select All’ button if you like) and paste it into the right-hand pane of the VBA editor "Module1" window
  4. Press Alt+F11 again to go back to Excel

...and points noted on closing the question
You haven't actually done that yet.
ASKER
Parkhill Mays

Hi MArtin:

Copy on all of the above except the button and assigning the macro.  If you can give me that answer I think that will do it.  Good on all else.  thanks a ton
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Parkhill Mays

Martin - i may just be a clutz at this - I am getting a message now that says System Error & H80070057 when I try to run it
I have the code in both Module 1 and Sheet1 (which is what I named my worksheet with the button on it).  I assigned the macro through the shape (although it was hard picking the right one).  Am I missing any steps with naming the button (Hide worksheets or show worksheets?)  I don't see any instruction pertaining to that
ASKER
Parkhill Mays

your sample sheet at the top of the VB window also has the words Option Explicit which is not in my code.  It also appears to be in a subwindow of the main window.  Its a little different looking than than the VB box in my version
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Parkhill Mays

Martin I inserted the words Hide Worksheets in the button (do I also need to get Show worksheets in there somehow, and if so how?)

Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Parkhill Mays

ok thank you.  If I have more trouble I will get some help from one of our engineers.  I think they should be able to assist and finish with me

thank you for the help an I will close the question now
Martin Liss

If you have more troubles don't hesitate to talk about them here.

In any case thank you for the testimonial and you’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Distinguished Expert in Excel 2018
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
              Experts Exchange Top Expert VBA 2018 to 2020