Make sheets visible/hidden based on in box dropdown

[Golly181]

Hello,

I have seen similar questions to this in the past but i have one slight difference.

I would like to use a drop down selection box which will make a sheet either visible or hidden based on the selection of "yes" or "no".

On sheet 1 I would like to list all the sheet names in the workbook and then, if column B is yes, then the sheet is visible, if it is no then the sheet is hidden.

Sheet one would look like:

[TABLE="width: 121"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Sheet Name[/TD]
[TD]Selection[/TD]
[/TR]
[TR]
[TD]Sheet1[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Sheet2[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Sheet3[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Sheet4[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Sheet5[/TD]
[TD]Yes
[/TD]
[/TR]
</tbody>[/TABLE]

I do not want to hardcode the names into the macro because I want the freedom to use this quickly on multiple workbooks, so the code needs to be able to read the sheet name in column a, store it, then run though the sheet names to hide that specific sheet.

Thanks in advance.

I will keep trying and post here if I come up with an answer.

 

[V_Malkoti]

Try this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim ws As Worksheet, sNames As String
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "[COLOR=#0000ff]Sheet1[/COLOR]" Then sNames = sNames & ws.Name & ","
    Next
    If Len(sNames & "") > 0 Then sNames = Left(sNames, Len(sNames) - 1)
    
    Application.EnableEvents = False
    
    If Sh.Name = "[COLOR=#0000ff]Sheet1[/COLOR]" And Target.Address = "[COLOR=#0000ff]$A$1[/COLOR]" Then
        If UCase(Target.Offset(0, 1).Value & "") = "YES" Then Sheets(Target.Value).Visible = xlSheetHidden
        If UCase(Target.Offset(0, 1).Value & "") = "NO" Then Sheets(Target.Value).Visible = xlSheetVisible
    End If
    
    With Sheets("Sheet1").Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=sNames
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    
    Application.EnableEvents = True
End Sub

This code will create a drop-down in cell A1 of Sheet1 and when a value is selected in that drop-down make a sheet visible (yes) or invisible (no) based on value in cell B1

 

[nahidking]

1. [TABLE="width: 272"]
<tbody>[TR]
[TD]Sheet Name[/TD]
[TD] B[/TD]
[TD] C[/TD]
[/TR]
[TR]
[TD]Sheet8[/TD]
[TD="align: left"]

<tbody>
</tbody>

[/TD]
[TD]Unhide[/TD]
[/TR]
[TR]
[TD="align: left"]

Sheet7

<tbody>
</tbody>

[/TD]
[TD][/TD]
[TD]Unhide[/TD]
[/TR]
[TR]
[TD]Sheet6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sorry I cant made it better than this.

1. First make a list of sheets in Column A. In column B insert combobox and add values hide and unhide for each sheet. {like above}
2. Link comboboxes with column C.
3. Add a "done" command button and use if-else function with below code

Hiding:
ActiveWorkbook.Sheets("sheet1").Visible = False
Unhiding:
ActiveWorkbook.Sheets("sheet1").Visible = True


this two simple code.

If you have very basic knowledge of VBA, I know u got the point.