+ Reply to Thread
Results 1 to 4 of 4

Macro to hide/unhide user defined specific sheets

  1. #1
    Registered User
    Join Date
    01-08-2014
    Location
    Bath, UK
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    84

    Macro to hide/unhide user defined specific sheets

    Hi,

    I’m trying to hide/unhide the sheets in a workbook, but allow the user to choose which ones are hidden/unhidden. I’m trying to have a flexible list of sheets which the user can amend by themselves, along with a series of buttons they can click which will only show the required sheets and hide the sheets they don’t wish to see.

    I’ve enclosed a spreadsheet which hopefully illustrates the layout a bit better…

    In column A, I have a list of all the sheets, created be a macro sourced elsewhere – let’s say, “Employee 1”, “Employee 2”, “Employee 3”, etc.
    Please Login or Register  to view this content.
    In columns C to M, for example (let’s leave column B empty), the user will move the names of various sheets into the appropriate column (or group of sheets) – e.g. to indicate which team they’re in. Let’s say the columns are headed “Team A”, “Team B”, “Team C”, “Team D” etc. and “Project 1”, “Project 2”, “Project 3” etc.

    “Employee 1” could be moved from A2 (the list of all sheets) into D2 (Team B) and copied into K2 to denote they’re in, say, Project 2

    “Employee 2” could be moved from A3 into C3 (if the user goes for the easy copy and paste), but could be moved into C2 if that cell is empty, to try to create a more concise/tidy list without any gaps), along with being copied into J3 (for project 1)

    Depending how the user copies the sheet name across, there could be gaps in each column (each Team list or Project list) – the user might have just copied the sheet name from the cell (say A31) and pasted into the same row in column F (i.e. F31) rather than doing any “tidying up” of the list.

    Although they would usually only be in one team and one project, each person can be in multiple teams and can also be in multiple projects.



    Is it possible to have a series of macros (or a flexible macro) which can go down Team A’s column and hide all the sheets in the workbook, unless it’s listed in Team A. The same would be needed for the subsequent columns – if the sheet is in that column’s list, keep it visible, but hide all the other sheets.

    Thanks,
    Graham
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    05-12-2018
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    258

    Re: Macro to hide/unhide user defined specific sheets

    hide al the employee sheets when opening the file (Placed in the thisworkbook-Object)

    Please Login or Register  to view this content.
    And for team A ( Column C) :

    Please Login or Register  to view this content.

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    5,943

    Re: Macro to hide/unhide user defined specific sheets

    Rather than copying employee names around, why not enter "Y" in col B for the sheets that need to be visible?
    Would that be an option?

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    5,943

    Re: Macro to hide/unhide user defined specific sheets

    If you're happy with the above option, try
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1