Worksheet_Change(ByVal Target As Range) not working with Protect Sheet

[trillium]

Hi
I have a macro to hide/unhide columns and update charts on a sheet. There are some areas where users can update information, but the rest I want locked so they can't accidentally mess up the formulas.

I tried inserting ActiveSheet.Unprotect at the very beginning and ActiveSheet.Protect at the very end, but didn't work.

I think looked through here and tried "Me.unprotect and then Me.Protect along with Application.EnableEvents = False at the beginning and True at the end... it will now unprotect once, hide/unhide columns and update charts but if I try selecting a new value, throws up a dialogue box that says "The cell or chart you are trying to change is on a protected sheet..." and I've noticed it's not reprotecting the sheet.

I have Unlocked all the charts already too.

Any thoughts? (I didn't put the full code in, it's very very long)

Private Sub Worksheet_Change(ByVal Target As Range)
' Jan 2019 Dava McKay
' HideRegion Macro
' Hide regions based on value in drop down list, charts will update
 Application.ScreenUpdating = False
 Application.EnableEvents = False
 On Error Resume Next
 Me.Unprotect
 
 
 If Target.Address(True, True) = "$A$3" Then
 
    
   Select Case Target
'''''''''''''''''''''''''''''''''''''''''''''''
   Case "Select Region"
   'Unhide
    Columns("C:CH").Select
    Selection.EntireColumn.Hidden = False
    Range("B4").Select


    'Change Names of Months
   'Change colour of Names of Months
    'JAN
    Range("C4").Select
      With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With

 

[Fluff]

What happens if you remove this line of code

On Error Resume Next

 

[trillium]

Hi Fluff
I removed it and get the same message. It just doesn't seem to want to re-protect the sheet and then I get that message box...

 

[Fluff]

That message box indicates that the sheet is protected & that you are probably trying to change a locked cell.
You need to ensure that cells like A3 are un-locked.

 

[trillium]

That's IT! That's the only cell I forgot to unlock! It's where the user selects which data/chart set to view. I feel so stupid.
Thank you!

 

[Fluff]

You're welcome & thanks for the feedback