[SOLVED] Drop down List to hide rows from one sheet to others

Drop down List to hide rows from one sheet to others

I want to have a drop down list with the name for example calculations, other and with choosing one I can have certain rows hidden on another sheet. Is there an easy way to do that?

I tried some codes but I do not understand the syntax since I am pulling from another sheet the cell value and changing on to three more sheets

Private Sub Worksheet_Change(ByVal Target As Range)
    Sheets("Project&Site_Details").Rows("4:8").EntireRow.Hidden = IIf(Me.Range("c4").Value2 = "Server Migration", True, False)
End Sub

This one keeps asking me for macro name.

So the basic idea is a dropdown on sheet Project&Site_Details on cell C4 with three names Server Migration, Data Type, Other. and have them hide or show rows on three different sheets according to the sheet. like Pre_Checklist, Post_Checklist

I got it to work using this code

Private Sub worksheet_change(ByVal target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("C4")
    
    If Not Application.Intersect(KeyCells, Range(target.Address)) Is Nothing Then
        If KeyCells = "Server Migration" Then
            Sheets(3).Rows("4:4").Hidden = True
            Sheets(3).Rows("5:5").Hidden = True
        Else
            Sheets(3).Rows("4:4").Hidden = False
            Sheets(3).Rows("5:5").Hidden = False
        End If
        If KeyCells = "Add-On" Then
            Sheets(3).Rows("10:11").Hidden = True
        Else
            Sheets(3).Rows("10:11").Hidden = False
        End If
    
    End If
    
End Sub

Is there any simpler way?

Solution

Consider using Select Case when evaluating a single value. Watch: Excel VBA Introduction Part 14 - Select Case Statements

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(False, False) = "C4" Then
        Select Case Target.Value
            Case "Server Migration"
                Sheets(3).Rows("4:4").Hidden = True
                Sheets(3).Rows("5:5").Hidden = True
            Case "Add-On"
                Sheets(3).Rows("10:11").Hidden = True
            Case Else
                Sheets(3).Rows("4:4").Hidden = False
                Sheets(3).Rows("5:5").Hidden = False
                Sheets(3).Rows("10:11").Hidden = False
        End Select
    End If
End Sub