Issue with unprotecting main sheet

[ave10]

I have a couple of protected worksheets in a workbook and when I right-click on the sheet name and unprotect the sheet, columns I have specifically protected in that sheet, go back to being protected after I do one action on the sheet. That one "action" could be, clicking into a cell that has a formula, which was previously protected, making a slight adjustment to the formula, clicking enter, then the sheet will go back to being protected and I have unprotect it again. It can even be, after unprotecting the sheet, I move from cell C4 and click on cell F8, the sheet will go back to being protected. I know the password, I enter it successfully every time and I've locked other columns in specific worksheets with the same password. Almost every sheet, has some vba code in it.


Is there a way to permanently unprotect these sheets? Or am I doing something wrong when I try to unprotect them?




Thanks in advance!

 

[jim may]

Is there Code in the Sheets Module? If so, post it.

 

[ave10]

This is the code that is posted in the main sheet I want to unprotect. The sheet is called "JE":

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MySheet As String
Worksheets("JE").Protect Password:="ImportLCPA17", UserInterfaceOnly:=True
    On Error GoTo Done:
    If Target.Column = 6 And Target.Cells.Count = 1 Then
        MySheet = WorksheetFunction.VLookup(Target.Offset(0, -2).Value, Sheets("ref_list").Range("$C$1:$D$17"), 2, 0)
        Sheets(MySheet).Visible = True
        Sheets(MySheet).Activate
    End If


Done:
Sheets("acct_codes").Visible = 0
'Sheets("deptlookup").Visible = 0


'Call Back_to_JE


End Sub

This is the code in the 'ThisWorkbook' object:

Sub hideAllSheets()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

    If ws.Name <> ThisWorkbook.ActiveSheet.Name Then

    ws.Visible = xlSheetHidden
    End If

    Next ws
End Sub

If this wasn't what you were looking for or would like other code/info. let me know. Thanks!

 

[jim may]

You say that "after I do one action on the sheet"

The Event-Code Worksheet_SelectionChange instructs Excel to "FIRE" and run the code therein if you only click a single cell (make a selection) on the Worksheet, so that is why things are going the way you describe.

This is the Line that is immediately Re-Protecting Your Sheet: Worksheets("JE").Protect Password:="ImportLCPA17", UserInterfaceOnly:=True

 

[ave10]

Thank you, Jim. I commented that line out and it seems to be working now.

I do have one more question, however. In my sheet "JE", I have two columns, columns E & G that have formulas and I ONLY want to lock/protect the cells in those columns. Would you happen to know how I can just lock/protect those rows?

Thanks again!

 

[jim may]

You mention originally selecting cell F8. Since cell F8 is in Column 6 this meets the criteria (as true) of the remaining code still active. Im a light-weight on your last Q, so unable to help. Perhaps someone else can assist you. One last observation... the code on the THISWORKBOOK looks to me like it belongs in a standard module. You need to determine if you actually need this macro and how/when to call it.
Good luck. Jim