Double click on locked cell unlocks sheet

L

lonybrown

Board Regular
Joined
Mar 8, 2021
Messages
68
Office Version
  1. 365
Platform
  1. Windows
I have a password protected sheet. When you double click on a unlocked cell and then press entre the sheet unlocks (no password required).
How can I prevent the sheet from unlocking this way?
 
Sort by date Sort by votes
I cant seem to get the XL2BB to run. But In the XLSTART folder there is only one file named PERSONAL.XLSB
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Michael M said:
Please post ANY code that is in that Sheet module ??
Click to expand...
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
ActiveSheet.Unprotect "MM"
If Not Application.Intersect(Range("B1"), Range(Target.Address)) Is Nothing Then

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
ActiveSheet.Protect "MM", True, True


ActiveWorkbook.Save
End If
End Sub
 
Upvote 0
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
ActiveSheet.Unprotect "MM"
If Not Application.Intersect(Range("B1"), Range(Target.Address)) Is Nothing Then

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
ActiveSheet.Protect "MM", True, True


ActiveWorkbook.Save
End If
End Sub
 
Upvote 0
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
 
Upvote 0
Solution
Jeffrey Mahoney said:
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
Click to expand...
Thanks Jeffery it works great!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Incorrect Password still runs macro
Replies
3
Views
243
PaulWJ
P
J
  • Question
VBA to unlock sheet
Replies
2
Views
187
Peter_SSs
Peter_SSs
N
  • Question
Locking a row after info entered
Replies
1
Views
460
RustamMika
R
N
  • Solved
Password protect column with unlock button
Replies
4
Views
394
DanteAmor
DanteAmor
N
  • Question
Locking a row once a value is entered into a cell
Replies
9
Views
703
arturojab
A

Forum statistics

Threads
1,214,932
Messages
6,122,332
Members
449,077
Latest member
jmsotelo

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