Private Sub Hide/unhide-compile error: procedure too large response

cairo95

Board Regular
Joined
Dec 11, 2007
Messages
91
Greetings,

I am working on a spreadsheet for risk assessment entries, the spreadsheet will have specified areas for up to 20 hazards with 12 risks for each hazard.
I will need specified rows to hide or unhide based on the numeric value of a cell in column A. i have received excellent help and was able to get some functionality working in Private Sub Worksheet_Change (ByValue Target as Range) but now i am getting a compile error: procedure too large response.

Is there a way to streamline the below code with private sub?

All triggers will be in column A

Starting in column A row 50 (the default is Row 50:53, 56:59, 71:74 will always be unhidden)
Each incident will have 1 Hazard and a max of 12 Risks for a max of 20 hazards with 12 risks each.

Therefore:

If row 50 = 1 then (this is hazard 1)
Unhide rows 97:100, 113:116, 128:131 (unhide hazard 2)
Else hide rows 97:142 (if not then hide hazard 2)
End if

If row 50 = 1 and row 53 =1 then (when 1 is entered in row 53 (risk 1) this unhides the row below risk 1 (54) to allow entry for risk 2 if needed and so on; up to risk 12 if needed.
Unhide rows 54, 69, 84 (unhide risk 2)
End if
Elseif row 50 = 1 and row 54 =2 then
Unhide rows 55, 70, 85 (unhide risk 3)
End if
Elseif row 50 = 1 and row 55 =3 then
Unhide rows 56, 71, 86 (unhide risk 4)
End if
Elseif row 50 = 1 and row 56 =4 then
Unhide rows 57, 72, 87 (unhide risk 5)
End if
Elseif row 50 = 1 and row 57 =5 then
Unhide rows 58, 73, 88 (unhide risk 6)
End if
Elseif row 50 = 1 and row 58 =6 then
Unhide rows 59, 74, 89 (unhide risk 7)
End if
Elseif row 50 = 1 and row 59 =7 then
Unhide rows 60, 75, 90 (unhide risk 8)
End if
Elseif row 50 = 1 and row 60 =8 then
Unhide rows 61, 76, 91(unhide risk 9)
End if
Elseif row 50 = 1 and row 61 =9 then
Unhide rows 62, 77, 92 (unhide risk 10)
End if
Elseif row 50 = 1 and row 62 =10 then
Unhide rows 63, 78, 93 (unhide risk 11)
End if
Elseif row 50 = 1 and row 63 =11 then
Unhide rows 64, 79, 94 (unhide risk 12)
End if
Elseif row 50 = 1 and row 64 =12 then
Unhide rows 65, 80, 95

The above is hazard 1 with potential for 12 risk entries. This will need to be repeated for up to the 20th Hazard. The pattern here is each Hazard begins every 47th row after the 50th row. The same with the risks…so the next hazard will begin in row 97..etc. :eek:
Your help will be greatly appreciated!

thanks in advance!
Cairo95
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Are you checking/using Target in the code to see what's been changed?
 
Upvote 0
Hello Norie,

thank you for responding,

below is a smidget of the code i'm using:


Code:
Private Sub Worksheet_Change(ByVal Target As Range)


 If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("$A53:A$64")) Is Nothing Then
      Target.Offset(1).EntireRow.Hidden = IIf(Target = "", True, False)

End If

Application.ScreenUpdating = False
    Application.EnableEvents = True

If Range("A54").Value = "" Then
Rows("70").EntireRow.Hidden = True
Rows("85").EntireRow.Hidden = True
ElseIf Range("A54").Value <> "" Then
Rows("70").EntireRow.Hidden = False
Rows("85").EntireRow.Hidden = False

End If
 
Upvote 0
Can you post a few more lines of code?
 
Upvote 0
Thanks Norie,

its mostly the same, it think this is part of the problem as the code is too long...see below for 2 hazards:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)


 If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("$A53:A$64")) Is Nothing Then
      Target.Offset(1).EntireRow.Hidden = IIf(Target = "", True, False)

End If

Application.ScreenUpdating = False
    Application.EnableEvents = True

If Range("A54").Value = "" Then
Rows("70").EntireRow.Hidden = True
Rows("85").EntireRow.Hidden = True
ElseIf Range("A54").Value <> "" Then
Rows("70").EntireRow.Hidden = False
Rows("85").EntireRow.Hidden = False

End If

If Range("A55").Value = "" Then
Rows("71").EntireRow.Hidden = True
Rows("86").EntireRow.Hidden = True
ElseIf Range("A55").Value <> "" Then
Rows("71").EntireRow.Hidden = False
Rows("86").EntireRow.Hidden = False

End If

If Range("A56").Value = "" Then
Rows("72").EntireRow.Hidden = True
Rows("87").EntireRow.Hidden = True
ElseIf Range("A56").Value <> "" Then
Rows("72").EntireRow.Hidden = False
Rows("87").EntireRow.Hidden = False

End If

If Range("A57").Value = "" Then
Rows("73").EntireRow.Hidden = True
Rows("88").EntireRow.Hidden = True
ElseIf Range("A57").Value <> "" Then
Rows("73").EntireRow.Hidden = False
Rows("88").EntireRow.Hidden = False

End If

If Range("A58").Value = "" Then
Rows("74").EntireRow.Hidden = True
Rows("89").EntireRow.Hidden = True
ElseIf Range("A58").Value <> "" Then
Rows("74").EntireRow.Hidden = False
Rows("89").EntireRow.Hidden = False

End If

If Range("A59").Value = "" Then
Rows("75").EntireRow.Hidden = True
Rows("90").EntireRow.Hidden = True
ElseIf Range("A59").Value <> "" Then
Rows("75").EntireRow.Hidden = False
Rows("90").EntireRow.Hidden = False

End If

If Range("A60").Value = "" Then
Rows("76").EntireRow.Hidden = True
Rows("91").EntireRow.Hidden = True
ElseIf Range("A60").Value <> "" Then
Rows("76").EntireRow.Hidden = False
Rows("91").EntireRow.Hidden = False

End If

If Range("A61").Value = "" Then
Rows("77").EntireRow.Hidden = True
Rows("92").EntireRow.Hidden = True
ElseIf Range("A61").Value <> "" Then
Rows("77").EntireRow.Hidden = False
Rows("92").EntireRow.Hidden = False

End If

If Range("A62").Value = "" Then
Rows("78").EntireRow.Hidden = True
Rows("93").EntireRow.Hidden = True
ElseIf Range("A62").Value <> "" Then
Rows("78").EntireRow.Hidden = False
Rows("93").EntireRow.Hidden = False

End If

If Range("A63").Value = "" Then
Rows("79").EntireRow.Hidden = True
Rows("94").EntireRow.Hidden = True
ElseIf Range("A63").Value <> "" Then
Rows("79").EntireRow.Hidden = False
Rows("94").EntireRow.Hidden = False

End If

If Range("A64").Value = "" Then
Rows("80").EntireRow.Hidden = True
Rows("95").EntireRow.Hidden = True
ElseIf Range("A64").Value <> "" Then
Rows("80").EntireRow.Hidden = False
Rows("95").EntireRow.Hidden = False

End If

 Application.EnableEvents = True
    Application.ScreenUpdating = True

    
    'Hazard 2--------------------------------------------------------------------
    
   If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("$A100:A$111")) Is Nothing Then
      Target.Offset(1).EntireRow.Hidden = IIf(Target = "", True, False)

End If

Application.ScreenUpdating = False
    Application.EnableEvents = True

If Range("A101").Value = "" Then
Rows("117").EntireRow.Hidden = True
Rows("132").EntireRow.Hidden = True
ElseIf Range("A101").Value <> "" Then
Rows("117").EntireRow.Hidden = False
Rows("132").EntireRow.Hidden = False

End If

If Range("A102").Value = "" Then
Rows("118").EntireRow.Hidden = True
Rows("133").EntireRow.Hidden = True
ElseIf Range("A102").Value <> "" Then
Rows("118").EntireRow.Hidden = False
Rows("133").EntireRow.Hidden = False

End If

If Range("A103").Value = "" Then
Rows("119").EntireRow.Hidden = True
Rows("134").EntireRow.Hidden = True
ElseIf Range("A103").Value <> "" Then
Rows("119").EntireRow.Hidden = False
Rows("134").EntireRow.Hidden = False

End If

If Range("A104").Value = "" Then
Rows("120").EntireRow.Hidden = True
Rows("135").EntireRow.Hidden = True
ElseIf Range("A104").Value <> "" Then
Rows("120").EntireRow.Hidden = False
Rows("135").EntireRow.Hidden = False

End If

If Range("A105").Value = "" Then
Rows("121").EntireRow.Hidden = True
Rows("136").EntireRow.Hidden = True
ElseIf Range("A105").Value <> "" Then
Rows("121").EntireRow.Hidden = False
Rows("136").EntireRow.Hidden = False

End If

If Range("A106").Value = "" Then
Rows("122").EntireRow.Hidden = True
Rows("137").EntireRow.Hidden = True
ElseIf Range("A106").Value <> "" Then
Rows("122").EntireRow.Hidden = False
Rows("137").EntireRow.Hidden = False

End If

If Range("A107").Value = "" Then
Rows("123").EntireRow.Hidden = True
Rows("138").EntireRow.Hidden = True
ElseIf Range("A107").Value <> "" Then
Rows("123").EntireRow.Hidden = False
Rows("138").EntireRow.Hidden = False

End If

If Range("A108").Value = "" Then
Rows("124").EntireRow.Hidden = True
Rows("139").EntireRow.Hidden = True
ElseIf Range("A108").Value <> "" Then
Rows("124").EntireRow.Hidden = False
Rows("139").EntireRow.Hidden = False

End If

If Range("A109").Value = "" Then
Rows("125").EntireRow.Hidden = True
Rows("140").EntireRow.Hidden = True
ElseIf Range("A109").Value <> "" Then
Rows("125").EntireRow.Hidden = False
Rows("140").EntireRow.Hidden = False

End If

If Range("A110").Value = "" Then
Rows("126").EntireRow.Hidden = True
Rows("141").EntireRow.Hidden = True
ElseIf Range("A110").Value <> "" Then
Rows("126").EntireRow.Hidden = False
Rows("141").EntireRow.Hidden = False

End If

If Range("A111").Value = "" Then
Rows("127").EntireRow.Hidden = True
Rows("142").EntireRow.Hidden = True
ElseIf Range("A111").Value <> "" Then
Rows("127").EntireRow.Hidden = False
Rows("142").EntireRow.Hidden = False

End If

 Application.EnableEvents = True
    Application.ScreenUpdating = True

thanks again!

Cairo95
 
Upvote 0
You can shorten it like
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("$A53:A$64")) Is Nothing Then
      Target.Offset(1).EntireRow.Hidden = IIf(Target = "", True, False)
   End If
   
   Application.ScreenUpdating = False
   Application.EnableEvents = True
   
   Range("70:70,85:85").EntireRow.Hidden = Range("A54").Value = ""
   Range("71:71,86:86").EntireRow.Hidden = Range("A55").Value = ""
End Sub
 
Upvote 0
Hello Fluff,

here is more of what i am trying to do. below is a snippit of 3 hazard....when the first hazard cell (A50) is populated with the number 1 (for the 1st Hazard) then i want the selected rows in Hazard 2 to reveal themselves for input, the if hazard 2 cell is populated with 2...then selected cells in Hazard 3 should reveal for imput if needed....im stuck...thank you for helping!

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("$A53:A$64")) Is Nothing Then
      Target.Offset(1).EntireRow.Hidden = IIf(Target = "", True, False)
  
   Application.ScreenUpdating = False
   Application.EnableEvents = True
   
   Range("70:70,85:85").EntireRow.Hidden = Range("A54").Value = ""
   Range("71:71,86:86").EntireRow.Hidden = Range("A55").Value = ""
   
   Range("72:72,87:87").EntireRow.Hidden = Range("A56").Value = ""
   Range("73:73,88:88").EntireRow.Hidden = Range("A57").Value = ""

   Range("74:74,89:89").EntireRow.Hidden = Range("A58").Value = ""
   Range("75:75,90:90").EntireRow.Hidden = Range("A59").Value = ""
   
   Range("76:76,91:91").EntireRow.Hidden = Range("A60").Value = ""
   Range("77:77,92:92").EntireRow.Hidden = Range("A61").Value = ""
   
   Range("78:78,93:93").EntireRow.Hidden = Range("A62").Value = ""
   Range("79:79,94:94").EntireRow.Hidden = Range("A63").Value = ""
   
   Range("80:80,95:95").EntireRow.Hidden = Range("A64").Value = ""

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    
 
'if A50 has a value then unhide input cells haz 2

If Range("A50").Value = "" Then Exit Sub
If Not Intersect(Target, Range("97:101,113:116,128:131")) Is Nothing Then
      Target.Offset(1).EntireRow.Hidden = IIf(Target = "", True, False)
      

Application.ScreenUpdating = False
Application.EnableEvents = True


    
'Hazard 2--------------------------------------------------------------------
    
   If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("$A100:A$111")) Is Nothing Then
      Target.Offset(1).EntireRow.Hidden = IIf(Target = "", True, False)


    Application.ScreenUpdating = False
    Application.EnableEvents = True


   Range("117:117,132:132").EntireRow.Hidden = Range("A101").Value = ""
   Range("118:118,133:133").EntireRow.Hidden = Range("A102").Value = ""
   
   Range("119:119,134:134").EntireRow.Hidden = Range("A103").Value = ""
   Range("120:120,135:135").EntireRow.Hidden = Range("A104").Value = ""

   Range("121:121,136:136").EntireRow.Hidden = Range("A105").Value = ""
   Range("122:122,137:137").EntireRow.Hidden = Range("A106").Value = ""
   
   Range("123:123,138:138").EntireRow.Hidden = Range("A107").Value = ""
   Range("124:124,139:139").EntireRow.Hidden = Range("A108").Value = ""
   
   Range("125:125,140:140").EntireRow.Hidden = Range("A109").Value = ""
   Range("126:126,141:141").EntireRow.Hidden = Range("A110").Value = ""
   
   Range("127:127,142:142").EntireRow.Hidden = Range("A111").Value = ""

    Application.EnableEvents = True
    Application.ScreenUpdating = True

'if A97 has a value then unhide input cells haz 3
If Range("A97").Value = "" Then Exit Sub
If Not Intersect(Target, Range("144:148,160:163,175:178")) Is Nothing Then
      Target.Offset(1).EntireRow.Hidden = IIf(Target = "", True, False)

Application.ScreenUpdating = False
    Application.EnableEvents = True



'Hazard 3------------------------------------------------------------------

If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("$A147:A$158")) Is Nothing Then
      Target.Offset(1).EntireRow.Hidden = IIf(Target = "", True, False)


    Application.ScreenUpdating = False
    Application.EnableEvents = True

   Range("164:164,179:179").EntireRow.Hidden = Range("A148").Value = ""
   Range("165:165,180:180").EntireRow.Hidden = Range("A149").Value = ""
   
   Range("166:166,181:181").EntireRow.Hidden = Range("A150").Value = ""
   Range("167:167,182:182").EntireRow.Hidden = Range("A151").Value = ""

   Range("168:168,183:183").EntireRow.Hidden = Range("A152").Value = ""
   Range("169:169,184:184").EntireRow.Hidden = Range("A153").Value = ""
   
   Range("170:170,185:185").EntireRow.Hidden = Range("A154").Value = ""
   Range("171:171,186:186").EntireRow.Hidden = Range("A155").Value = ""
   
   Range("172:172,187:187").EntireRow.Hidden = Range("A156").Value = ""
   Range("173:173,188:188").EntireRow.Hidden = Range("A157").Value = ""
   
   Range("174:174,189:189").EntireRow.Hidden = Range("A158").Value = ""

   Application.EnableEvents = True
   Application.ScreenUpdating = True
    
'if A144 has a value then unhide input cells haz 4
If Range("A144").Value = "" Then Exit Sub
If Not Intersect(Target, Range("191:195,207:210,222:225")) Is Nothing Then
      Target.Offset(1).EntireRow.Hidden = IIf(Target = "", True, False)
'
End If
 End If
Application.ScreenUpdating = False
    Application.EnableEvents = True

end sub
 
Upvote 0
How about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.CountLarge > 1 Then Exit Sub
   Application.ScreenUpdating = False
   Application.EnableEvents = True

   If Not Intersect(Target, Range("$A53:A$64")) Is Nothing Then
      Target.Offset(1).EntireRow.Hidden = IIf(Target = "", True, False)
   
      Range("70:70,85:85").EntireRow.Hidden = Range("A54").Value = ""
      Range("71:71,86:86").EntireRow.Hidden = Range("A55").Value = ""
      
      Range("72:72,87:87").EntireRow.Hidden = Range("A56").Value = ""
      Range("73:73,88:88").EntireRow.Hidden = Range("A57").Value = ""
   
      Range("74:74,89:89").EntireRow.Hidden = Range("A58").Value = ""
      Range("75:75,90:90").EntireRow.Hidden = Range("A59").Value = ""
      
      Range("76:76,91:91").EntireRow.Hidden = Range("A60").Value = ""
      Range("77:77,92:92").EntireRow.Hidden = Range("A61").Value = ""
      
      Range("78:78,93:93").EntireRow.Hidden = Range("A62").Value = ""
      Range("79:79,94:94").EntireRow.Hidden = Range("A63").Value = ""
      
      Range("80:80,95:95").EntireRow.Hidden = Range("A64").Value = ""
   End If
 
'if A50 has a value then unhide input cells haz 2

   If Not Intersect(Target, Range("A50")) Is Nothing Then
      Range("97:101,113:116,128:131").EntireRow.Hidden = IIf(Target = "", True, False)
   End If

'Hazard 2--------------------------------------------------------------------
    
   If Not Intersect(Target, Range("$A100:A$111")) Is Nothing Then
      Target.Offset(1).EntireRow.Hidden = IIf(Target = "", True, False)

      Range("117:117,132:132").EntireRow.Hidden = Range("A101").Value = ""
      Range("118:118,133:133").EntireRow.Hidden = Range("A102").Value = ""
   
      Range("119:119,134:134").EntireRow.Hidden = Range("A103").Value = ""
      Range("120:120,135:135").EntireRow.Hidden = Range("A104").Value = ""
   
      Range("121:121,136:136").EntireRow.Hidden = Range("A105").Value = ""
      Range("122:122,137:137").EntireRow.Hidden = Range("A106").Value = ""
   
      Range("123:123,138:138").EntireRow.Hidden = Range("A107").Value = ""
      Range("124:124,139:139").EntireRow.Hidden = Range("A108").Value = ""
   
      Range("125:125,140:140").EntireRow.Hidden = Range("A109").Value = ""
      Range("126:126,141:141").EntireRow.Hidden = Range("A110").Value = ""
   
      Range("127:127,142:142").EntireRow.Hidden = Range("A111").Value = ""
   End If

'if A97 has a value then unhide input cells haz 3
   If Not Intersect(Target, Range("A97")) Is Nothing Then
      Range("144:148,160:163,175:178").EntireRow.Hidden = IIf(Target = "", True, False)
   End If

'Hazard 3------------------------------------------------------------------

   If Not Intersect(Target, Range("$A147:A$158")) Is Nothing Then
      Target.Offset(1).EntireRow.Hidden = IIf(Target = "", True, False)

      Range("164:164,179:179").EntireRow.Hidden = Range("A148").Value = ""
      Range("165:165,180:180").EntireRow.Hidden = Range("A149").Value = ""
   
      Range("166:166,181:181").EntireRow.Hidden = Range("A150").Value = ""
      Range("167:167,182:182").EntireRow.Hidden = Range("A151").Value = ""
   
      Range("168:168,183:183").EntireRow.Hidden = Range("A152").Value = ""
      Range("169:169,184:184").EntireRow.Hidden = Range("A153").Value = ""
   
      Range("170:170,185:185").EntireRow.Hidden = Range("A154").Value = ""
      Range("171:171,186:186").EntireRow.Hidden = Range("A155").Value = ""
   
      Range("172:172,187:187").EntireRow.Hidden = Range("A156").Value = ""
      Range("173:173,188:188").EntireRow.Hidden = Range("A157").Value = ""
   
      Range("174:174,189:189").EntireRow.Hidden = Range("A158").Value = ""
   End If

'if A144 has a value then unhide input cells haz 4
   If Intersect(Target, Range("A144")) Is Nothing Then
      Range("191:195,207:210,222:225").EntireRow.Hidden = IIf(Target = "", True, False)
   End If
Application.ScreenUpdating = False
Application.EnableEvents = True

End Sub
 
Upvote 0
this works perfectly! was able to do all 20 Hazards and 12 risk without any "Error, to Large" response.

thanks so much Fluff!

Regards,

Cairo95
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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
Back
Top