Excel VBA Code + Error when trying to run multiple scripts in one work sheet.

Dear All,

Thank you in advance for your time.
I am having trouble with a code that i compiled that is supposed to do 4 things in successive order in a worksheet that I have. The problem is that it is not working past the first code.
In order of desired operations:
Code 1 - Code 1 is supposed to look at the value in a drop down (Yes or No) and hide specified rows. This code works and hides the rows are requested.
Code 2 - Code 2 is supposed to look at another drop down and say if No, hide the following rows. This code works too, if i use it just by itself
Code 3 - Code 3 is supposed to look at a third drop down (Yes or No) and hide rows 44 : 45, and rows 50 : 59
Code 4 - Code 4 is supposed to look at a numerical value in a cell, and then based on this numerical value, do the following:

If the numerical value is 1, then hide rows 50 :59;
If the numerical value is 2, then hide rows 51 : 59:
If the numerical value is 3, then hide rows 52:59……………

Up to if the numerical value is 10, then no rows.

The code that I constructed is below, however only the first code is working.
Please review and advise what I am doing wrong.
I have researched possible answers, but they all return errors when I try to incorporate them into my code.

Thank you all once again and I appreciate any help.


-Warm regards,

Blckhandsid3


Aforementioned code:

Please Login or Register  to view this content.

Option Explicit

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Private Sub worksheet_Change(ByVal Target As Range)
Call Script1(Target)
Call Script2(Target)
all Script3(Target)
Call Script4(Target)
End Sub

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Private Sub Script1(ByVal Target As Range)
If Target.Address = "$B$24" Then
If UCase(Range("B24").Value) = "YES" Then
Rows("25:41").EntireRow.Hidden = False
ElseIf UCase(Range("B24").Value) = "NO" Then
Rows("25:41").EntireRow.Hidden = True
End If
End If
End Sub

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Private Sub Script2(ByVal Target As Range)
If Target.Address = "$B$25" Then
If UCase(Range("B25").Value) = "YES" Then
Rows("36:40").EntireRow.Hidden = False
ElseIf UCase(Range("B25").Value) = "NO" Then
Rows("36:40").EntireRow.Hidden = True
End If
End If
End Sub

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Private Sub Script3(ByVal Target As Range)
If Target.Address = "$B$43" Then
If UCase(Range("B43").Value) = "YES" Then
Rows("44:59").EntireRow.Hidden = False
ElseIf UCase(Range("B43").Value) = "NO" Then
Rows("44:45").EntireRow.Hidden = True
Rows("50:59").EntireRow.Hidden = True
End If
End If
End Sub

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Private Sub Script4(ByVal Target As Range)
If Intersect(Target, Range("B44")) Then
Exit Sub
Select Case Target.Value
Case 1
Range("44:49").EntireRow.Hidden = False
Range("50:59").EntireRow.Hidden = True
Case 2
Range("44:50").EntireRow.Hidden = False
Range("51:59").EntireRow.Hidden = True
Case 3
Range("44:51").EntireRow.Hidden = False
Range("52:59").EntireRow.Hidden = True
Case 4
Range("44:52").EntireRow.Hidden = False
Range("53:59").EntireRow.Hidden = True
Case 5
Range("44:53").EntireRow.Hidden = False
Range("54:59").EntireRow.Hidden = True
Case 6
Range("44:54").EntireRow.Hidden = False
Range("55:59").EntireRow.Hidden = True
Case 7
Range("44:55").EntireRow.Hidden = False
Range("56:59").EntireRow.Hidden = True
Case 8
Range("44:56").EntireRow.Hidden = False
Range("57:59").EntireRow.Hidden = True
Case 9
Range("44:57").EntireRow.Hidden = False
Range("58:59").EntireRow.Hidden = True
Case 10
Range("44:58").EntireRow.Hidden = False
Range("51:59").EntireRow.Hidden = True
Case 11
Range("44:59").EntireRow.Hidden = False
Range("60:61").EntireRow.Hidden = True
End Select
End If
End Sub

Please Login or Register  to view this content.

Could you please enclose the code in code tags?

Then it'll be easier to read.

By the way, why 4 different subs all taking the same argument?

They could all be combined in the Worksheet_Change event.