Hide Unhide specific sheets with cell value

**aksnave** · 03-21-2012, 07:13 PM

Hello, I am trying to hide/unhide sheets based on whether or not there is text in specific cells. I can get the first command to work, but cannot get the remaining statements to execute. Here is a sample of the first 3 [out of 40 statements] from my code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A13")) Is Nothing Then
    If IsEmpty(Range("A13")) Then
        Sheets("BOM DTX 1").Visible = xlSheetHidden
    Else
        Sheets("BOM DTX 1").Visible = xlSheetVisible
    End If
End Sub

Private Sub Worksheet_Change1(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A25")) Is Nothing Then
    If IsEmpty(Range("A25")) Then
        Sheets("BOM DTX 2").Visible = xlSheetHidden
    Else
        Sheets("BOM DTX 2").Visible = xlSheetVisible
    End If
End If
End Sub


Private Sub Worksheet_Change2(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A36")) Is Nothing Then
    If IsEmpty(Range("A36")) Then
        Sheets("BOM DTX 3").Visible = xlSheetHidden
    Else
        Sheets("BOM DTX 3").Visible = xlSheetVisible
    End If
End If
End Sub

Thank you,
Aaron

**dangelor** · 03-21-2012, 07:28 PM

I think you may have more success with just one change sub and using a Select Case statement...

**aksnave** · 03-23-2012, 06:12 PM

Thank you for the reply. I am a novice at VBA. Am I on the right track here? And if so, how do I get it to continue to the next hide/unhide based on contents of next cell/range in the same sheet?

Sub Hide-Unhide()
Select Case Range("A13")
     Case Is = Empty
        Sheets ("BOM DTX 1").Hidden = True
     Case Else <> Empty
        Sheets ("BOM DTX 1"). Hidden = False
End Select
End Sub

**dangelor** · 03-26-2012, 10:43 AM

Select case may not be the best way. Try adapting this...

Private Sub Worksheet_Change(ByVal Target As Range)
    Sheets("BOM DTX 1").Visible = IsEmpty(Range("A13"))
    Sheets("BOM DTX 2").Visible = IsEmpty(Range("A25"))
    Sheets("BOM DTX 3").Visible = IsEmpty(Range("A36"))
End Sub

**aksnave** · 03-27-2012, 05:05 PM

Thank you dangelor...that is super clean! I adjusted it to below so the sheet is hidden if cell is empty and it works fantastic!

Private Sub Worksheet_Change(ByVal Target As Range)
    Sheets("BOM DTX 1").Visible = Not IsEmpty(Range("A13"))
    Sheets("BOM DTX 2").Visible = Not IsEmpty(Range("A25"))
    Sheets("BOM DTX 3").Visible = Not IsEmpty(Range("A36"))
End Sub