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.
Your help will be greatly appreciated!
thanks in advance!
Cairo95
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.
Your help will be greatly appreciated!
thanks in advance!
Cairo95
