Hide/Unhide sheets when a cell has a certain value

**Magical** · 03-26-2012, 02:21 PM

Hello all,

Please be patient with me. I am completely brand new to VBA. I have a work task that requires me to make a certain sheet hide when a certain value is chosen from a Data Validation. Can anyone give me a simple code that allows me to do this? It would be greatly appreciated.

Many thanks,
Magical

**Pichingualas** · 03-26-2012, 02:46 PM

Paste this in ThisWorkbook code sheet in vba and change the sheets name according to your needs:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    If Sh.Name = "SheetName" And Target.Column = 1 And Target.Row = 3 Then
        If Target.Value = 10 Then
            Sheets("HideShowSheet").Visible = False
        Else
            Sheets("HideShowSheet").Visible = True
        End If
    End If
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

SheetName should be changed to the name of the sheet which contains the cell that will determine if your controlled sheet should be hidden or not.
The 1 from Target.Column should be changed to the column number of the cell that contains the value.
The 3 from Target.Row should be changed to the row number of the cell that contains the value.
The 10 from Target.Value should be changed to the value that the cell should have to hide the sheet.
HideShowSheet should change to the name of the sheet you want to hide or show according to the cell value.

It is very important that this code is pasted on the ThisWorkbook code sheet from vba or it won't work.

I hope that helps.