VBA protected sheet problem

D

Dan5977

Board Regular
Joined
Aug 7, 2008
Messages
111
Office Version
  1. 2010
Hi,

I've made a spreadsheet which includes some VBA code that works very well.

Now I want to share it with a lot of users. Of course I would like to protect cells to help stop users from breaking formulas, messing up the formatting, etc.

This is where I am stuck. After protecting the worksheet, I am getting a Run-time error '1004' when using the protected sheet. It says "Unable to set the Hidden property of the Range class".

I presume I can add a bit more code to deal with this problem but have no idea where to start. The rows that will be hidden will have locked cells, which I still want to be hidden depending on the selections made.

I'm a bit of a noob with VBA as you can tell. I was happy to get the functions working on an unprotected sheet at my first attempt but this has really got me stumped.

The code I have is as follows:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, Range("F6:J7, B12:K12")) Is Nothing Then Exit Sub
 
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual

Rows("1:286").Hidden = False
    Select Case Range("F6").Value
    Case "0"
     Rows("23:35").Hidden = True
     Case "1"
     Rows("26:34").Hidden = True
     Case "2"
     Rows("27:34").Hidden = True
     Case "3"
     Rows("28:34").Hidden = True
     Case "4"
     Rows("29:34").Hidden = True
     Case "5"
     Rows("30:34").Hidden = True
     Case "6"
     Rows("31:34").Hidden = True
     Case "7"
     Rows("32:34").Hidden = True
     Case "8"
     Rows("33:34").Hidden = True
     Case "9"
     Rows("34").Hidden = True
End Select

    Select Case Range("G6").Value
     Case "0"
     Rows("36:48").Hidden = True
     Case "1"
     Rows("39:47").Hidden = True
     Case "0"
     Rows("40:47").Hidden = True
     Case "0"
     Rows("41:47").Hidden = True
     Case "0"
     Rows("42:47").Hidden = True
     Case "0"
     Rows("43:47").Hidden = True
     Case "0"
     Rows("44:47").Hidden = True
     Case "0"
     Rows("45:47").Hidden = True
     Case "0"
     Rows("46:47").Hidden = True
     Case "0"
     Rows("47").Hidden = True
End Select

    Select Case Range("H6").Value
    Case "0"
     Rows("49:61").Hidden = True
     Case "1"
     Rows("52:60").Hidden = True
     Case "2"
     Rows("53:60").Hidden = True
     Case "3"
     Rows("54:60").Hidden = True
     Case "4"
     Rows("55:60").Hidden = True
     Case "5"
     Rows("56:60").Hidden = True
     Case "6"
     Rows("57:60").Hidden = True
     Case "7"
     Rows("58:60").Hidden = True
     Case "8"
     Rows("59:60").Hidden = True
     Case "9"
     Rows("60").Hidden = True
End Select

    Select Case Range("I6").Value
     Case "0"
     Rows("62:74").Hidden = True
     Case "1"
     Rows("65:73").Hidden = True
     Case "2"
     Rows("66:73").Hidden = True
     Case "3"
     Rows("67:73").Hidden = True
     Case "4"
     Rows("68:73").Hidden = True
     Case "5"
     Rows("69:73").Hidden = True
     Case "6"
     Rows("70:73").Hidden = True
     Case "7"
     Rows("71:73").Hidden = True
     Case "8"
     Rows("72:73").Hidden = True
     Case "9"
     Rows("73").Hidden = True
End Select

    Select Case Range("J6").Value
     Case "0"
     Rows("75:87").Hidden = True
     Case "1"
     Rows("78:86").Hidden = True
     Case "2"
     Rows("79:86").Hidden = True
     Case "3"
     Rows("80:86").Hidden = True
     Case "4"
     Rows("81:86").Hidden = True
     Case "5"
     Rows("82:86").Hidden = True
     Case "6"
     Rows("83:86").Hidden = True
     Case "7"
     Rows("84:86").Hidden = True
     Case "8"
     Rows("85:86").Hidden = True
     Case "9"
     Rows("86").Hidden = True
End Select

    Select Case Range("F7").Value
     Case "0"
     Rows("90:102").Hidden = True
     Case "1"
     Rows("93:101").Hidden = True
     Case "2"
     Rows("94:101").Hidden = True
     Case "3"
     Rows("95:101").Hidden = True
     Case "4"
     Rows("96:101").Hidden = True
     Case "5"
     Rows("97:101").Hidden = True
     Case "6"
     Rows("98:101").Hidden = True
     Case "7"
     Rows("99:101").Hidden = True
     Case "8"
     Rows("100:101").Hidden = True
     Case "9"
     Rows("101").Hidden = True
End Select

    Select Case Range("G7").Value
     Case "0"
     Rows("103:115").Hidden = True
     Case "1"
     Rows("106:114").Hidden = True
     Case "2"
     Rows("107:114").Hidden = True
     Case "3"
     Rows("108:114").Hidden = True
     Case "4"
     Rows("109:114").Hidden = True
     Case "5"
     Rows("110:114").Hidden = True
     Case "6"
     Rows("111:114").Hidden = True
     Case "7"
     Rows("112:114").Hidden = True
     Case "8"
     Rows("113:114").Hidden = True
     Case "9"
     Rows("114").Hidden = True
End Select

    Select Case Range("H7").Value
     Case "0"
     Rows("116:128").Hidden = True
     Case "1"
     Rows("119:127").Hidden = True
     Case "2"
     Rows("120:127").Hidden = True
     Case "3"
     Rows("121:127").Hidden = True
     Case "4"
     Rows("122:127").Hidden = True
     Case "5"
     Rows("123:127").Hidden = True
     Case "6"
     Rows("124:127").Hidden = True
     Case "7"
     Rows("125:127").Hidden = True
     Case "8"
     Rows("126:127").Hidden = True
     Case "9"
     Rows("127").Hidden = True
End Select

    Select Case Range("I7").Value
     Case "0"
     Rows("129:141").Hidden = True
     Case "1"
     Rows("132:140").Hidden = True
     Case "2"
     Rows("133:140").Hidden = True
     Case "3"
     Rows("134:140").Hidden = True
     Case "4"
     Rows("135:140").Hidden = True
     Case "5"
     Rows("136:140").Hidden = True
     Case "6"
     Rows("137:140").Hidden = True
     Case "7"
     Rows("138:140").Hidden = True
     Case "8"
     Rows("139:140").Hidden = True
     Case "9"
     Rows("140").Hidden = True
End Select

    Select Case Range("J7").Value
     Case "0"
     Rows("142:154").Hidden = True
     Case "1"
     Rows("145:153").Hidden = True
     Case "2"
     Rows("146:153").Hidden = True
     Case "3"
     Rows("147:153").Hidden = True
     Case "4"
     Rows("148:153").Hidden = True
     Case "5"
     Rows("149:153").Hidden = True
     Case "6"
     Rows("150:153").Hidden = True
     Case "7"
     Rows("151:153").Hidden = True
     Case "8"
     Rows("152:153").Hidden = True
     Case "9"
     Rows("153").Hidden = True
End Select

    Select Case Range("B12").Value
     Case "0"
     Rows("157:169").Hidden = True
     Case "1"
     Rows("160:168").Hidden = True
     Case "2"
     Rows("161:168").Hidden = True
     Case "3"
     Rows("162:168").Hidden = True
     Case "4"
     Rows("163:168").Hidden = True
     Case "5"
     Rows("164:168").Hidden = True
     Case "6"
     Rows("165:168").Hidden = True
     Case "7"
     Rows("166:168").Hidden = True
     Case "8"
     Rows("167:168").Hidden = True
     Case "9"
     Rows("168").Hidden = True
End Select

    Select Case Range("C12").Value
     Case "0"
     Rows("170:182").Hidden = True
     Case "1"
     Rows("173:181").Hidden = True
     Case "2"
     Rows("174:181").Hidden = True
     Case "3"
     Rows("175:181").Hidden = True
     Case "4"
     Rows("176:181").Hidden = True
     Case "5"
     Rows("177:181").Hidden = True
     Case "6"
     Rows("178:181").Hidden = True
     Case "7"
     Rows("179:181").Hidden = True
     Case "8"
     Rows("180:181").Hidden = True
     Case "9"
     Rows("181").Hidden = True
End Select

    Select Case Range("D12").Value
     Case "0"
     Rows("183:195").Hidden = True
     Case "1"
     Rows("186:194").Hidden = True
     Case "2"
     Rows("187:194").Hidden = True
     Case "3"
     Rows("188:194").Hidden = True
     Case "4"
     Rows("189:194").Hidden = True
     Case "5"
     Rows("190:194").Hidden = True
     Case "6"
     Rows("191:194").Hidden = True
     Case "7"
     Rows("192:194").Hidden = True
     Case "8"
     Rows("193:194").Hidden = True
     Case "9"
     Rows("194").Hidden = True
End Select

    Select Case Range("E12").Value
     Case "0"
     Rows("196:208").Hidden = True
     Case "1"
     Rows("199:207").Hidden = True
     Case "2"
     Rows("200:207").Hidden = True
     Case "3"
     Rows("201:207").Hidden = True
     Case "4"
     Rows("202:207").Hidden = True
     Case "5"
     Rows("203:207").Hidden = True
     Case "6"
     Rows("204:207").Hidden = True
     Case "7"
     Rows("205:207").Hidden = True
     Case "8"
     Rows("206:207").Hidden = True
     Case "9"
     Rows("207").Hidden = True
End Select

    Select Case Range("F12").Value
     Case "0"
     Rows("209:221").Hidden = True
     Case "1"
     Rows("212:220").Hidden = True
     Case "2"
     Rows("213:220").Hidden = True
     Case "3"
     Rows("214:220").Hidden = True
     Case "4"
     Rows("215:220").Hidden = True
     Case "5"
     Rows("216:220").Hidden = True
     Case "6"
     Rows("217:220").Hidden = True
     Case "7"
     Rows("218:220").Hidden = True
     Case "8"
     Rows("219:220").Hidden = True
     Case "9"
     Rows("220").Hidden = True
End Select

    Select Case Range("G12").Value
     Case "0"
     Rows("222:234").Hidden = True
     Case "1"
     Rows("225:233").Hidden = True
     Case "2"
     Rows("226:233").Hidden = True
     Case "3"
     Rows("227:233").Hidden = True
     Case "4"
     Rows("228:233").Hidden = True
     Case "5"
     Rows("229:233").Hidden = True
     Case "6"
     Rows("230:233").Hidden = True
     Case "7"
     Rows("231:233").Hidden = True
     Case "8"
     Rows("232:233").Hidden = True
     Case "9"
     Rows("233").Hidden = True
End Select

    Select Case Range("H12").Value
     Case "0"
     Rows("235:247").Hidden = True
     Case "1"
     Rows("238:246").Hidden = True
     Case "2"
     Rows("239:246").Hidden = True
     Case "3"
     Rows("240:246").Hidden = True
     Case "4"
     Rows("241:246").Hidden = True
     Case "5"
     Rows("242:246").Hidden = True
     Case "6"
     Rows("243:246").Hidden = True
     Case "7"
     Rows("244:246").Hidden = True
     Case "8"
     Rows("245:246").Hidden = True
     Case "9"
     Rows("246").Hidden = True
End Select

    Select Case Range("I12").Value
     Case "0"
     Rows("248:260").Hidden = True
     Case "1"
     Rows("251:259").Hidden = True
     Case "2"
     Rows("252:259").Hidden = True
     Case "3"
     Rows("253:259").Hidden = True
     Case "4"
     Rows("254:259").Hidden = True
     Case "5"
     Rows("255:259").Hidden = True
     Case "6"
     Rows("256:259").Hidden = True
     Case "7"
     Rows("257:259").Hidden = True
     Case "8"
     Rows("258:259").Hidden = True
     Case "9"
     Rows("259").Hidden = True
End Select

    Select Case Range("J12").Value
     Case "0"
     Rows("261:273").Hidden = True
     Case "1"
     Rows("264:272").Hidden = True
     Case "2"
     Rows("265:272").Hidden = True
     Case "3"
     Rows("266:272").Hidden = True
     Case "4"
     Rows("267:272").Hidden = True
     Case "5"
     Rows("268:272").Hidden = True
     Case "6"
     Rows("269:272").Hidden = True
     Case "7"
     Rows("270:272").Hidden = True
     Case "8"
     Rows("271:272").Hidden = True
     Case "9"
     Rows("272").Hidden = True
End Select

    Select Case Range("K12").Value
     Case "0"
     Rows("274:286").Hidden = True
     Case "1"
     Rows("277:285").Hidden = True
     Case "2"
     Rows("278:285").Hidden = True
     Case "3"
     Rows("279:285").Hidden = True
     Case "4"
     Rows("280:285").Hidden = True
     Case "5"
     Rows("281:285").Hidden = True
     Case "6"
     Rows("282:285").Hidden = True
     Case "7"
     Rows("283:285").Hidden = True
     Case "8"
     Rows("284:285").Hidden = True
     Case "9"
     Rows("285").Hidden = True
End Select

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Thanks in advance for any help.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Try adding the lines in red, substituting abc with the actual password

Rich (BB code): 
Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, Range("F6:J7, B12:K12")) Is Nothing Then Exit Sub
Me.Unprotect Password:="abc"
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual

Rows("1:286").Hidden = False
    Select Case Range("F6").Value
    Case "0"
     Rows("23:35").Hidden = True
     Case "1"
     Rows("26:34").Hidden = True
     Case "2"
     Rows("27:34").Hidden = True
     Case "3"
     Rows("28:34").Hidden = True
     Case "4"
     Rows("29:34").Hidden = True
     Case "5"
     Rows("30:34").Hidden = True
     Case "6"
     Rows("31:34").Hidden = True
     Case "7"
     Rows("32:34").Hidden = True
     Case "8"
     Rows("33:34").Hidden = True
     Case "9"
     Rows("34").Hidden = True
End Select

    Select Case Range("G6").Value
     Case "0"
     Rows("36:48").Hidden = True
     Case "1"
     Rows("39:47").Hidden = True
     Case "0"
     Rows("40:47").Hidden = True
     Case "0"
     Rows("41:47").Hidden = True
     Case "0"
     Rows("42:47").Hidden = True
     Case "0"
     Rows("43:47").Hidden = True
     Case "0"
     Rows("44:47").Hidden = True
     Case "0"
     Rows("45:47").Hidden = True
     Case "0"
     Rows("46:47").Hidden = True
     Case "0"
     Rows("47").Hidden = True
End Select

    Select Case Range("H6").Value
    Case "0"
     Rows("49:61").Hidden = True
     Case "1"
     Rows("52:60").Hidden = True
     Case "2"
     Rows("53:60").Hidden = True
     Case "3"
     Rows("54:60").Hidden = True
     Case "4"
     Rows("55:60").Hidden = True
     Case "5"
     Rows("56:60").Hidden = True
     Case "6"
     Rows("57:60").Hidden = True
     Case "7"
     Rows("58:60").Hidden = True
     Case "8"
     Rows("59:60").Hidden = True
     Case "9"
     Rows("60").Hidden = True
End Select

    Select Case Range("I6").Value
     Case "0"
     Rows("62:74").Hidden = True
     Case "1"
     Rows("65:73").Hidden = True
     Case "2"
     Rows("66:73").Hidden = True
     Case "3"
     Rows("67:73").Hidden = True
     Case "4"
     Rows("68:73").Hidden = True
     Case "5"
     Rows("69:73").Hidden = True
     Case "6"
     Rows("70:73").Hidden = True
     Case "7"
     Rows("71:73").Hidden = True
     Case "8"
     Rows("72:73").Hidden = True
     Case "9"
     Rows("73").Hidden = True
End Select

    Select Case Range("J6").Value
     Case "0"
     Rows("75:87").Hidden = True
     Case "1"
     Rows("78:86").Hidden = True
     Case "2"
     Rows("79:86").Hidden = True
     Case "3"
     Rows("80:86").Hidden = True
     Case "4"
     Rows("81:86").Hidden = True
     Case "5"
     Rows("82:86").Hidden = True
     Case "6"
     Rows("83:86").Hidden = True
     Case "7"
     Rows("84:86").Hidden = True
     Case "8"
     Rows("85:86").Hidden = True
     Case "9"
     Rows("86").Hidden = True
End Select

    Select Case Range("F7").Value
     Case "0"
     Rows("90:102").Hidden = True
     Case "1"
     Rows("93:101").Hidden = True
     Case "2"
     Rows("94:101").Hidden = True
     Case "3"
     Rows("95:101").Hidden = True
     Case "4"
     Rows("96:101").Hidden = True
     Case "5"
     Rows("97:101").Hidden = True
     Case "6"
     Rows("98:101").Hidden = True
     Case "7"
     Rows("99:101").Hidden = True
     Case "8"
     Rows("100:101").Hidden = True
     Case "9"
     Rows("101").Hidden = True
End Select

    Select Case Range("G7").Value
     Case "0"
     Rows("103:115").Hidden = True
     Case "1"
     Rows("106:114").Hidden = True
     Case "2"
     Rows("107:114").Hidden = True
     Case "3"
     Rows("108:114").Hidden = True
     Case "4"
     Rows("109:114").Hidden = True
     Case "5"
     Rows("110:114").Hidden = True
     Case "6"
     Rows("111:114").Hidden = True
     Case "7"
     Rows("112:114").Hidden = True
     Case "8"
     Rows("113:114").Hidden = True
     Case "9"
     Rows("114").Hidden = True
End Select

    Select Case Range("H7").Value
     Case "0"
     Rows("116:128").Hidden = True
     Case "1"
     Rows("119:127").Hidden = True
     Case "2"
     Rows("120:127").Hidden = True
     Case "3"
     Rows("121:127").Hidden = True
     Case "4"
     Rows("122:127").Hidden = True
     Case "5"
     Rows("123:127").Hidden = True
     Case "6"
     Rows("124:127").Hidden = True
     Case "7"
     Rows("125:127").Hidden = True
     Case "8"
     Rows("126:127").Hidden = True
     Case "9"
     Rows("127").Hidden = True
End Select

    Select Case Range("I7").Value
     Case "0"
     Rows("129:141").Hidden = True
     Case "1"
     Rows("132:140").Hidden = True
     Case "2"
     Rows("133:140").Hidden = True
     Case "3"
     Rows("134:140").Hidden = True
     Case "4"
     Rows("135:140").Hidden = True
     Case "5"
     Rows("136:140").Hidden = True
     Case "6"
     Rows("137:140").Hidden = True
     Case "7"
     Rows("138:140").Hidden = True
     Case "8"
     Rows("139:140").Hidden = True
     Case "9"
     Rows("140").Hidden = True
End Select

    Select Case Range("J7").Value
     Case "0"
     Rows("142:154").Hidden = True
     Case "1"
     Rows("145:153").Hidden = True
     Case "2"
     Rows("146:153").Hidden = True
     Case "3"
     Rows("147:153").Hidden = True
     Case "4"
     Rows("148:153").Hidden = True
     Case "5"
     Rows("149:153").Hidden = True
     Case "6"
     Rows("150:153").Hidden = True
     Case "7"
     Rows("151:153").Hidden = True
     Case "8"
     Rows("152:153").Hidden = True
     Case "9"
     Rows("153").Hidden = True
End Select

    Select Case Range("B12").Value
     Case "0"
     Rows("157:169").Hidden = True
     Case "1"
     Rows("160:168").Hidden = True
     Case "2"
     Rows("161:168").Hidden = True
     Case "3"
     Rows("162:168").Hidden = True
     Case "4"
     Rows("163:168").Hidden = True
     Case "5"
     Rows("164:168").Hidden = True
     Case "6"
     Rows("165:168").Hidden = True
     Case "7"
     Rows("166:168").Hidden = True
     Case "8"
     Rows("167:168").Hidden = True
     Case "9"
     Rows("168").Hidden = True
End Select

    Select Case Range("C12").Value
     Case "0"
     Rows("170:182").Hidden = True
     Case "1"
     Rows("173:181").Hidden = True
     Case "2"
     Rows("174:181").Hidden = True
     Case "3"
     Rows("175:181").Hidden = True
     Case "4"
     Rows("176:181").Hidden = True
     Case "5"
     Rows("177:181").Hidden = True
     Case "6"
     Rows("178:181").Hidden = True
     Case "7"
     Rows("179:181").Hidden = True
     Case "8"
     Rows("180:181").Hidden = True
     Case "9"
     Rows("181").Hidden = True
End Select

    Select Case Range("D12").Value
     Case "0"
     Rows("183:195").Hidden = True
     Case "1"
     Rows("186:194").Hidden = True
     Case "2"
     Rows("187:194").Hidden = True
     Case "3"
     Rows("188:194").Hidden = True
     Case "4"
     Rows("189:194").Hidden = True
     Case "5"
     Rows("190:194").Hidden = True
     Case "6"
     Rows("191:194").Hidden = True
     Case "7"
     Rows("192:194").Hidden = True
     Case "8"
     Rows("193:194").Hidden = True
     Case "9"
     Rows("194").Hidden = True
End Select

    Select Case Range("E12").Value
     Case "0"
     Rows("196:208").Hidden = True
     Case "1"
     Rows("199:207").Hidden = True
     Case "2"
     Rows("200:207").Hidden = True
     Case "3"
     Rows("201:207").Hidden = True
     Case "4"
     Rows("202:207").Hidden = True
     Case "5"
     Rows("203:207").Hidden = True
     Case "6"
     Rows("204:207").Hidden = True
     Case "7"
     Rows("205:207").Hidden = True
     Case "8"
     Rows("206:207").Hidden = True
     Case "9"
     Rows("207").Hidden = True
End Select

    Select Case Range("F12").Value
     Case "0"
     Rows("209:221").Hidden = True
     Case "1"
     Rows("212:220").Hidden = True
     Case "2"
     Rows("213:220").Hidden = True
     Case "3"
     Rows("214:220").Hidden = True
     Case "4"
     Rows("215:220").Hidden = True
     Case "5"
     Rows("216:220").Hidden = True
     Case "6"
     Rows("217:220").Hidden = True
     Case "7"
     Rows("218:220").Hidden = True
     Case "8"
     Rows("219:220").Hidden = True
     Case "9"
     Rows("220").Hidden = True
End Select

    Select Case Range("G12").Value
     Case "0"
     Rows("222:234").Hidden = True
     Case "1"
     Rows("225:233").Hidden = True
     Case "2"
     Rows("226:233").Hidden = True
     Case "3"
     Rows("227:233").Hidden = True
     Case "4"
     Rows("228:233").Hidden = True
     Case "5"
     Rows("229:233").Hidden = True
     Case "6"
     Rows("230:233").Hidden = True
     Case "7"
     Rows("231:233").Hidden = True
     Case "8"
     Rows("232:233").Hidden = True
     Case "9"
     Rows("233").Hidden = True
End Select

    Select Case Range("H12").Value
     Case "0"
     Rows("235:247").Hidden = True
     Case "1"
     Rows("238:246").Hidden = True
     Case "2"
     Rows("239:246").Hidden = True
     Case "3"
     Rows("240:246").Hidden = True
     Case "4"
     Rows("241:246").Hidden = True
     Case "5"
     Rows("242:246").Hidden = True
     Case "6"
     Rows("243:246").Hidden = True
     Case "7"
     Rows("244:246").Hidden = True
     Case "8"
     Rows("245:246").Hidden = True
     Case "9"
     Rows("246").Hidden = True
End Select

    Select Case Range("I12").Value
     Case "0"
     Rows("248:260").Hidden = True
     Case "1"
     Rows("251:259").Hidden = True
     Case "2"
     Rows("252:259").Hidden = True
     Case "3"
     Rows("253:259").Hidden = True
     Case "4"
     Rows("254:259").Hidden = True
     Case "5"
     Rows("255:259").Hidden = True
     Case "6"
     Rows("256:259").Hidden = True
     Case "7"
     Rows("257:259").Hidden = True
     Case "8"
     Rows("258:259").Hidden = True
     Case "9"
     Rows("259").Hidden = True
End Select

    Select Case Range("J12").Value
     Case "0"
     Rows("261:273").Hidden = True
     Case "1"
     Rows("264:272").Hidden = True
     Case "2"
     Rows("265:272").Hidden = True
     Case "3"
     Rows("266:272").Hidden = True
     Case "4"
     Rows("267:272").Hidden = True
     Case "5"
     Rows("268:272").Hidden = True
     Case "6"
     Rows("269:272").Hidden = True
     Case "7"
     Rows("270:272").Hidden = True
     Case "8"
     Rows("271:272").Hidden = True
     Case "9"
     Rows("272").Hidden = True
End Select

    Select Case Range("K12").Value
     Case "0"
     Rows("274:286").Hidden = True
     Case "1"
     Rows("277:285").Hidden = True
     Case "2"
     Rows("278:285").Hidden = True
     Case "3"
     Rows("279:285").Hidden = True
     Case "4"
     Rows("280:285").Hidden = True
     Case "5"
     Rows("281:285").Hidden = True
     Case "6"
     Rows("282:285").Hidden = True
     Case "7"
     Rows("283:285").Hidden = True
     Case "8"
     Rows("284:285").Hidden = True
     Case "9"
     Rows("285").Hidden = True
End Select
Me.Protect Password:="abc"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
 
Upvote 0
On another note, you could replace all those Select Cases' with a more mathematical approach.

For example....This

Code: 
Select Case Range("F6").Value
    Case "0"
     Rows("23:35").Hidden = True
     Case "1"
     Rows("26:34").Hidden = True
     Case "2"
     Rows("27:34").Hidden = True
     Case "3"
     Rows("28:34").Hidden = True
     Case "4"
     Rows("29:34").Hidden = True
     Case "5"
     Rows("30:34").Hidden = True
     Case "6"
     Rows("31:34").Hidden = True
     Case "7"
     Rows("32:34").Hidden = True
     Case "8"
     Rows("33:34").Hidden = True
     Case "9"
     Rows("34").Hidden = True
End Select

could be replaced with this...

Code: 
If Range("F6") = 0 Then Rows("23:35").Hidden = True
If Range("F6") <= 9 Then Rows("34").Offset(Range("F6") - 9).Resize(10 - Range("F6")).Hidden = True
 
Upvote 0
Thanks for that but as the other one is working, I think I'll leave as is.

I do have one other question though. When case "0" is selected I have rows hiding. Is there a way for this to apply the same command if the cell is left blank?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
VBA Case Error
Replies
2
Views
64
Ark68
A
G
  • Question Question
VBA help - copy cells from specific table to specific table on another sheet.
Replies
0
Views
760
GardianAngelUK
G
M
  • Question Question
Using VBA code in a protected excel sheet
Replies
2
Views
539
MagicCarpet
M
E
  • Solved
Custom Sorting in VBA
Replies
3
Views
607
Micron
M
W
  • Solved
Code for rows of cells returning reverse-order values.
Replies
5
Views
441
Worksong
W

Forum statistics

Threads
1,224,567
Messages
6,179,571
Members
452,927
Latest member
whitfieldcraig

Share this page

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