combine multiple Private Sub Worksheet_Change(ByVal Target As Range)

[Mandy_84]

Hi People
please help to combine multiple Private Sub Worksheet_Change(ByVal Target As Range) code

 

[Yongle]

Please click on icon <vba/> and paste your code inside the code tags
Cannot copy a picture of code into VBA editor

 

[Mandy_84]

[
Private Sub Worksheet_SelectionChange(ByVal Target As Range)



With Sheet1.DTPicker1

DTPicker1.Value = Format(DTPicker1.Value, "dd/mm/yyyy")

.Height = 20

.Width = 20

If Not Intersect(Target, Range("G24:G25")) Is Nothing Then

.Visible = True

.Top = Target.Top

.Left = Target.Offset(0, 1).Left

.LinkedCell = Target.Address

Else

.Visible = False

End If

End With







Private Sub Worksheet_Change(ByVal Target As Range)

ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="123"

'Unprotect



If Range("B20").Value = "Yes" Then

Rows("26:40").EntireRow.Hidden = False

End If



If Range("B20").Value = "No" Then



Rows("26:40").EntireRow.Hidden = True



ElseIf Range("B20").Value = 0 Then

Rows("26:40").EntireRow.Hidden = True





ThisWorkbook.Worksheets("Sheet1").Protect Password:="123"

'Protect

End If

End Sub





Private Sub Worksheet_Change(ByVal Target As Range)

ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="123"

'Unprotect



If Range("B22").Value <20 Then

Rows("23"). Hidden = False

Rows("41"). Hidden = False

End If



If Range("B22").Value >20 Then



Rows("23"). Hidden = True

Rows("41"). Hidden = True

End if



ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="123"

'Unprotect



If Range("B27").Value <20 Then

Rows("28"). Hidden = False

Rows("41"). Hidden = False

End If



If Range("B27").Value >20 Then



Rows("28"). Hidden = True

Rows("41"). Hidden = True

ThisWorkbook.Worksheets("Sheet1").Protect Password:="123"

'Protect

End If



ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="123"

'Unprotect



If Range("B32").Value <20 Then

Rows("33"). Hidden = False

Rows("41"). Hidden = False

End If



If Range("B32").Value >20 Then



Rows("33"). Hidden = True

Rows("41"). Hidden = True



ThisWorkbook.Worksheets("Sheet1").Protect Password:="123"

'Protect

End If



ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="123"

'Unprotect



If Range("B37").Value <20 Then

Rows("38"). Hidden = False

Rows("41"). Hidden = False

End If



If Range("B37").Value >20 Then



Rows("38"). Hidden = True

Rows("41"). Hidden = True



ThisWorkbook.Worksheets("Sheet1").Protect Password:="123"

'Protect

End If


End Sub


]

 

[Yongle]

Q1
Row 41 had several rules applying to it
Which one prevails ?

If Range("B22").Value < 20 Then Rows("41").Hidden = False
If Range("B22").Value > 20 Then Rows("41").Hidden = True

If Range("B27").Value < 20 Then Rows("41").Hidden = False
If Range("B27").Value > 20 Then Rows("41").Hidden = True

If Range("B32").Value < 20 Then Rows("41").Hidden = False
If Range("B32").Value > 20 Then Rows("41").Hidden = True

If Range("B37").Value < 20 Then Rows("41").Hidden = False
If Range("B37").Value > 20 The Rows("41").Hidden = True

Q2
What happens if B22, B27, B32 or B37 = 20

 

[Mandy_84]

Hi Yongle,
A1 - Row 41 should appear in any of above rule
A2 - there is no possibility of =20 , thus only >20 & <20
thanks

 

[Yongle]

A1 - Row 41 should appear in any of above rule

- that sounds unreliable to me, but I guess it depends on which combination of values are possible in B22, B27, B32 or B37

Will post something when back at PC

 

[Yongle]

The change in hidden status is applied based on whichever cell value is edited
- so when B20 changes value only rows 26:40 hidden status considered
- and when B22 changes value only rows 23\41 hidden status considered
- etc

Please amend the value in each of the 5 cells once after installing the code, so that everything has been set based on what is in the code
Test after that and let me know if it not behaving as required

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, Hide As Boolean
    Dim T As Range: Set T = Target
    If T.CountLarge > 1 Then Exit Sub
    If T < 20 Then Hide = False Else Hide = True
    Select Case T.Address(0, 0)
        Case "B20": Set rng = Rows("26:40"): If T = "Yes" Then Hide = False Else Hide = True
        Case "B22": Set rng = Union(Rows(23), Rows(41))
        Case "B27": Set rng = Union(Rows(28), Rows(41))
        Case "B32": Set rng = Union(Rows(33), Rows(41))
        Case "B37": Set rng = Union(Rows(38), Rows(41))
    End Select
    rng.EntireRow.Hidden = Hide
End Sub