VBA IF statement to hide worksheet

**Schwartz** · 01-23-2012, 04:38 PM

Hello,
I have Excel 2010. I am new to VBA. I tried the "record a macro" function, but was unable to get it to do what I want. I am trying to write a macro to hide a worksheet if C$3="" in that worksheet. There are about 23 different sheets, and I am having trouble coding it. I would greatly appreciate any insight you may have.

**swoop99** · 01-23-2012, 04:53 PM

Open the vba editor and find the sheet(s) you want this to happen on in the top left window. Right click it and select 'view code' and paste this in:


Private Sub Worksheet_Change(ByVal Target As Range)

If Range("C3").Value = "" Then activesheet.Visible = False


End Sub

**Leith Ross** · 01-23-2012, 04:53 PM

Hello Schwartz,

The basic VBA statement would be. There are other methods but to know which one is best for your needs would require more information.

    If $C$3 = "" Then 
       ActiveSheet.Visible = False
    Else
       ActiveSheet.Visible = True
    End If

**Schwartz** · 01-23-2012, 05:01 PM

Thank you for the quick replies. Let me clarify exactly what I need. I apologize I did not specify earlier. The macro needs to Look at C3 of each sheet. If there is no value in C3, it needs to hide that sheet. If there is a value, It needs the tab to be renamed to the value in C3. I have the code to change the tab name, but I do not know how to incorporate the hiding function.

Private Sub Workbook_Open()
    Dim wSheet As Worksheet
    On Error Resume Next
    For Each wSheet In Me.Worksheets
        If wSheet.Range("C3") = "" Then
            wSheet.Name = "Sheet" & wSheet.Index
        Else
            wSheet.Name = Format(wSheet.Range("C3"), "mmm dd, yyyy")
        End If
    Next wSheet
    On Error GoTo 0
End Sub

**Schwartz** · 01-23-2012, 05:05 PM

Thank you for the quick replies. Let me clarify exactly what I need. I apologize I did not specify earlier. The macro needs to Look at C3 of each sheet. If there is no value in C3, it needs to hide that sheet. If there is a value, It needs the tab to be renamed to the value in C3. I have the code to change the tab name, but I do not know how to incorporate the hiding function.

Private Sub Workbook_Open()
    Dim wSheet As Worksheet
    On Error Resume Next
    For Each wSheet In Me.Worksheets
        If wSheet.Range("C3") = "" Then
            wSheet.Name = "Sheet" & wSheet.Index
        Else
            wSheet.Name = Format(wSheet.Range("C3"), "mmm dd, yyyy")
        End If
    Next wSheet
    On Error GoTo 0
End Sub