VBA - To have multiple private sub in a worksheet

[keshaun]

Hi,

I am a beginner to Excel and VBA, i am trying to create a script to expand/collapse via formula and hiding of worksheet via formula. I have tried to combine the 2 script but it is not working.

Can someone advise on how i should do it correctly?

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("$G1:G100")) Is Nothing Then If Range("G14").Value < 1 Then Rows("15:20").EntireRow.Hidden = True Else Rows("15:20").EntireRow.Hidden = False End If If Range("G21") < 1 Then Rows("22:26").EntireRow.Hidden = True Else Rows("22:26").EntireRow.Hidden = False End If If Range("G27") < 1 Then Rows("28:35").EntireRow.Hidden = True Else Rows("28:35").EntireRow.Hidden = False End If Private Sub Worksheet_CHANGE_A_Calculate() If Range("A2").Value < 1 And Not IsEmpty(Range("A2")) Then Sheets("PRELIMINARIES").Visible = xlSheetHidden Else Sheets("PRELIMINARIES").Visible = xlSheetVisible End If End Sub End If End Sub

[SamT]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("$G1:G100")) Is Nothing Then


If Range("G14").Value < 1 Then
Rows("15:20").EntireRow.Hidden = True
Else
Rows("15:20").EntireRow.Hidden = False
End If


If Range("G21") < 1 Then
Rows("22:26").EntireRow.Hidden = True
Else
Rows("22:26").EntireRow.Hidden = False
End If


If Range("G27") < 1 Then
Rows("28:35").EntireRow.Hidden = True
Else
Rows("28:35").EntireRow.Hidden = False
End If
End Sub

Private Sub Worksheet_CHANGE_A_Calculate()
If Range("A2").Value < 1 And Not IsEmpty(Range("A2")) Then
Sheets("PRELIMINARIES").Visible = xlSheetHidden
Else
Sheets("PRELIMINARIES").Visible = xlSheetVisible
End If


End Sub

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("G1:G100")) Is Nothing Then
         
        Rows("15:20").Hidden = Range("G14").Value < 1
        Rows("22:26").Hidden = Range("G21").Value < 1
        Rows("28:35").Hidden = Range("G27").Value < 1
    End If
End Sub
 
Private Sub Worksheet_Calculate()
    If Range("A2").Value < 1 And Not IsEmpty(Range("A2").Value) Then
        Worksheets("PRELIMINARIES").Visible = xlSheetHidden
    Else
        Worksheets("PRELIMINARIES").Visible = xlSheetVisible
    End If
End Sub