Make sheets visible/hidden based on in box dropdown

Golly181

New Member
Joined
Aug 17, 2015
Messages
1
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:

Sheet NameSelection
Sheet1Yes
Sheet2Yes
Sheet3Yes
Sheet4No
Sheet5Yes

<colgroup><col><col></colgroup><tbody>
</tbody>

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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

V_Malkoti

Well-known Member
Joined
Jun 10, 2015
Messages
898
Try this:

Code:
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

New Member
Joined
Aug 4, 2015
Messages
14
1.
Sheet Name B C
Sheet8
clip_image002.png

<tbody>
</tbody>
Unhide
Sheet7

<tbody>
</tbody>
Unhide
Sheet6
Sheet5
Sheet4
Sheet3
Sheet2

<tbody>
</tbody>

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,980
Messages
5,599,160
Members
414,295
Latest member
Dolenhil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top