Private Sub Worksheet_Change(ByVal Target As Range)Please post ANY code that is in that Sheet module ??
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("B1"), Target) Is Nothing Then
ActiveSheet.Unprotect "MM"
Application.ScreenUpdating = False
Application.EnableEvents = False
Columns("A:B").EntireColumn.Hidden = False
Columns("C:M").EntireColumn.Hidden = True
Columns("E:G").EntireColumn.Hidden = False
Columns("H:GQ").EntireColumn.Hidden = True
Columns("GR:GS").EntireColumn.Hidden = False
Rows("174:182").EntireRow.Hidden = True
Select Case Target.Value
Case Is = "1": Columns("H:K").EntireColumn.Hidden = False
Case Is = "2": Columns("L:O").EntireColumn.Hidden = False
Case Is = "3": Columns("P:S").EntireColumn.Hidden = False
Case Is = "4": Columns("T:W").EntireColumn.Hidden = False
Case Is = "5": Columns("X:AA").EntireColumn.Hidden = False
Case Is = "6": Columns("AB:AE").EntireColumn.Hidden = False
Case Is = "7": Columns("AF:AI").EntireColumn.Hidden = False
Case Is = "8": Columns("AJ:AM").EntireColumn.Hidden = False
Case Is = "9": Columns("AN:AQ").EntireColumn.Hidden = False
Case Is = "10": Columns("AR:AU").EntireColumn.Hidden = False
Case Is = "11": Columns("AV:AY").EntireColumn.Hidden = False
Case Is = "12": Columns("AZ:BC").EntireColumn.Hidden = False
Case Is = "13": Columns("BD:BG").EntireColumn.Hidden = False
Case Is = "14": Columns("BH:BK").EntireColumn.Hidden = False
Case Is = "15": Columns("BL:BO").EntireColumn.Hidden = False
Case Is = "16": Columns("BP:BS").EntireColumn.Hidden = False
Case Is = "17": Columns("BT:BW").EntireColumn.Hidden = False
Case Is = "18": Columns("BX:CA").EntireColumn.Hidden = False
Case Is = "19": Columns("CB:CE").EntireColumn.Hidden = False
Case Is = "20": Columns("CF:CI").EntireColumn.Hidden = False
Case Is = "21": Columns("CJ:CM").EntireColumn.Hidden = False
Case Is = "22": Columns("CN:CQ").EntireColumn.Hidden = False
Case Is = "23": Columns("CR:CU").EntireColumn.Hidden = False
Case Is = "24": Columns("CV:CY").EntireColumn.Hidden = False
Case Is = "25": Columns("CZ:DC").EntireColumn.Hidden = False
Case Is = "26": Columns("DD:DG").EntireColumn.Hidden = False
Case Is = "27": Columns("DH:DK").EntireColumn.Hidden = False
Case Is = "28": Columns("DL:DO").EntireColumn.Hidden = False
Case Is = "29": Columns("DP:DS").EntireColumn.Hidden = False
Case Is = "30": Columns("DT:DW").EntireColumn.Hidden = False
Case Is = "31": Columns("DX:EA").EntireColumn.Hidden = False
Case Is = "32": Columns("EB:EE").EntireColumn.Hidden = False
Case Is = "33": Columns("EF:EI").EntireColumn.Hidden = False
Case Is = "34": Columns("EJ:EM").EntireColumn.Hidden = False
Case Is = "35": Columns("EN:EQ").EntireColumn.Hidden = False
Case Is = "36": Columns("ER:EU").EntireColumn.Hidden = False
Case Is = "37": Columns("EV:EY").EntireColumn.Hidden = False
Case Is = "38": Columns("EZ:FC").EntireColumn.Hidden = False
Case Is = "39": Columns("FD:FG").EntireColumn.Hidden = False
Case Is = "40": Columns("FH:FK").EntireColumn.Hidden = False
Case Is = "41": Columns("FL:FO").EntireColumn.Hidden = False
Case Is = "42": Columns("FP:FS").EntireColumn.Hidden = False
Case Is = "43": Columns("FT:FW").EntireColumn.Hidden = False
Case Is = "44": Columns("FX:GA").EntireColumn.Hidden = False
Case Is = "45": Columns("GB:GE").EntireColumn.Hidden = False
Case Is = "46": Columns("GF:GI").EntireColumn.Hidden = False
Case Is = "47": Columns("GJ:GM").EntireColumn.Hidden = False
Case Is = "48": Columns("GN:GQ").EntireColumn.Hidden = False
End Select
Application.ScreenUpdating = True
Application.EnableEvents = True
ActiveSheet.Protect "MM", True, True
ActiveWorkbook.Save
End If
End Sub
Thanks Jeffery it works great!!In your code, the Line where you unlock the sheet was put prior to the test if cell B1 was altered. So every time a cell change was made it unlocked the sheet. I put the unprotect statement inside the IF test. I changed a couple other things and added some lines to improve speed.
VBA Code:Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("B1"), Target) Is Nothing Then ActiveSheet.Unprotect "MM" Application.ScreenUpdating = False Application.EnableEvents = False Columns("A:B").EntireColumn.Hidden = False Columns("C:M").EntireColumn.Hidden = True Columns("E:G").EntireColumn.Hidden = False Columns("H:GQ").EntireColumn.Hidden = True Columns("GR:GS").EntireColumn.Hidden = False Rows("174:182").EntireRow.Hidden = True Select Case Target.Value Case Is = "1": Columns("H:K").EntireColumn.Hidden = False Case Is = "2": Columns("L:O").EntireColumn.Hidden = False Case Is = "3": Columns("P:S").EntireColumn.Hidden = False Case Is = "4": Columns("T:W").EntireColumn.Hidden = False Case Is = "5": Columns("X:AA").EntireColumn.Hidden = False Case Is = "6": Columns("AB:AE").EntireColumn.Hidden = False Case Is = "7": Columns("AF:AI").EntireColumn.Hidden = False Case Is = "8": Columns("AJ:AM").EntireColumn.Hidden = False Case Is = "9": Columns("AN:AQ").EntireColumn.Hidden = False Case Is = "10": Columns("AR:AU").EntireColumn.Hidden = False Case Is = "11": Columns("AV:AY").EntireColumn.Hidden = False Case Is = "12": Columns("AZ:BC").EntireColumn.Hidden = False Case Is = "13": Columns("BD:BG").EntireColumn.Hidden = False Case Is = "14": Columns("BH:BK").EntireColumn.Hidden = False Case Is = "15": Columns("BL:BO").EntireColumn.Hidden = False Case Is = "16": Columns("BP:BS").EntireColumn.Hidden = False Case Is = "17": Columns("BT:BW").EntireColumn.Hidden = False Case Is = "18": Columns("BX:CA").EntireColumn.Hidden = False Case Is = "19": Columns("CB:CE").EntireColumn.Hidden = False Case Is = "20": Columns("CF:CI").EntireColumn.Hidden = False Case Is = "21": Columns("CJ:CM").EntireColumn.Hidden = False Case Is = "22": Columns("CN:CQ").EntireColumn.Hidden = False Case Is = "23": Columns("CR:CU").EntireColumn.Hidden = False Case Is = "24": Columns("CV:CY").EntireColumn.Hidden = False Case Is = "25": Columns("CZ:DC").EntireColumn.Hidden = False Case Is = "26": Columns("DD:DG").EntireColumn.Hidden = False Case Is = "27": Columns("DH:DK").EntireColumn.Hidden = False Case Is = "28": Columns("DL:DO").EntireColumn.Hidden = False Case Is = "29": Columns("DP:DS").EntireColumn.Hidden = False Case Is = "30": Columns("DT:DW").EntireColumn.Hidden = False Case Is = "31": Columns("DX:EA").EntireColumn.Hidden = False Case Is = "32": Columns("EB:EE").EntireColumn.Hidden = False Case Is = "33": Columns("EF:EI").EntireColumn.Hidden = False Case Is = "34": Columns("EJ:EM").EntireColumn.Hidden = False Case Is = "35": Columns("EN:EQ").EntireColumn.Hidden = False Case Is = "36": Columns("ER:EU").EntireColumn.Hidden = False Case Is = "37": Columns("EV:EY").EntireColumn.Hidden = False Case Is = "38": Columns("EZ:FC").EntireColumn.Hidden = False Case Is = "39": Columns("FD:FG").EntireColumn.Hidden = False Case Is = "40": Columns("FH:FK").EntireColumn.Hidden = False Case Is = "41": Columns("FL:FO").EntireColumn.Hidden = False Case Is = "42": Columns("FP:FS").EntireColumn.Hidden = False Case Is = "43": Columns("FT:FW").EntireColumn.Hidden = False Case Is = "44": Columns("FX:GA").EntireColumn.Hidden = False Case Is = "45": Columns("GB:GE").EntireColumn.Hidden = False Case Is = "46": Columns("GF:GI").EntireColumn.Hidden = False Case Is = "47": Columns("GJ:GM").EntireColumn.Hidden = False Case Is = "48": Columns("GN:GQ").EntireColumn.Hidden = False End Select Application.ScreenUpdating = True Application.EnableEvents = True ActiveSheet.Protect "MM", True, True ActiveWorkbook.Save End If End Sub
So, the Oscar goes to @Jeffrey Mahoney's post!Thanks Jeffery it works great!!