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.

    Sub Worksheet_list()
    Application.ScreenUpdating = False
    Range("A2:A1000").Select
    Selection.ClearContents
    Range("A1").Select
    Dim x As Integer
    For x = 1 To Worksheets.Count
    Cells(x + 1, 1).Value = Worksheets(x).Name
    Next x
    End Sub

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