PDA

View Full Version : VBA - To have multiple private sub in a worksheet



keshaun
01-05-2016, 08:05 AM
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
01-05-2016, 10:39 AM
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
01-05-2016, 11:36 AM
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