combine multiple Private Sub Worksheet_Change(ByVal Target As Range)

Mandy_84

Board Regular
Joined
May 29, 2017
Messages
71
Hi People :)
please help to combine multiple Private Sub Worksheet_Change(ByVal Target As Range) code

1593415832553.png
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Please click on icon <vba/> and paste your code inside the code tags
Cannot copy a picture of code into VBA editor
 

Mandy_84

Board Regular
Joined
May 29, 2017
Messages
71
VBA Code:
[
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 29, 2017
Messages
71

ADVERTISEMENT

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,870
Messages
5,627,366
Members
416,245
Latest member
Xterminat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top