VBA code not working when I protect the sheet

[ss_bb_24]

Good morning to all!!!

I have used the below code to make hide/unhide some columns in my excel sheet. It works fine. Now, I need to protrect the sheet except the coloured cells. So I unlocked the cell by clicking the property of the colored cells & then did this :- tools- protection-Protect sheet - protect unlocked cells.

But after protecting this sheet the macro code of hiding/unhiding of the columns are NOT WORKING & Errors is coming. I have used the below code:-

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Row = 2 And Target.Column = 3 Then
      If Target.Value = "Y" Then
         Range("g:y").Columns.Hidden = False
         Range("ac:ae").Columns.Hidden = True
         Range("z:ab").Columns.Hidden = False
        
      ElseIf Target.Value = "N" Then
         Range("g:y").Columns.Hidden = True
         Range("ac:ae").Columns.Hidden = False
         Range("z:ab").Columns.Hidden = True
         End If
   
   End If
End Sub

CAN ANYBODY HELP ME please.

I have attached a jpeg of my excel sheet.

[Paul]

Hi ss_bb,

You would need to unprotect your worksheet at the beginning of the macro, then re-protect it at the end. For example:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Row = 2 And Target.Column = 3 Then
   ActiveSheet.Unprotect Password:="your_password"
      If Target.Value = "Y" Then
         Range("g:y").Columns.Hidden = False
         Range("ac:ae").Columns.Hidden = True
         Range("z:ab").Columns.Hidden = False
        
      ElseIf Target.Value = "N" Then
         Range("g:y").Columns.Hidden = True
         Range("ac:ae").Columns.Hidden = False
         Range("z:ab").Columns.Hidden = True
         End If
   ActiveSheet.Protect Password:="your_password"   
   End If
End Sub

[ss_bb_24]

thanks pjoaquin. Its working in that sheet. But I am getting the other problem related to the same issue in the same workbook.

I have used some code code to the whole workbook so that during opening this workbook you are asked to give your name. I have used the below code:-

Private Sub Workbook_Open()
    'Prompt for name
    Dim Ans As Variant
    Ans = InputBox("Please enter your name.")
    
    'Close the file if no name entered
    If Ans = "" Then ThisWorkbook.Close False
    
    'Enter name and date in the sheet
    With Worksheets("Main Sheet")
        .Range("B2") = Ans
        .Range("C2") = Format(Now, "m/d/yyyy h:mm")
    End With
End Sub

Now the same value ( name of the person who opens this workbook) & date with time will be autometically written in the B2 & C2 cell of my Main sheet.Main sheet is also protected by password "007" & only blue colored cells are unprotected for feeding the date. Now when I open this workbook then error is coming in this .Range("B2") = Ans (bold & underlined in the code). But when I add

ActiveSheet.Unprotect Password:="007"

&

ActiveSheet.Protect Password:="007"

in the above code then its working but all the cells in main sheet are getting unlocked.

I have given a screen picture of my main sheet. COULD ANYONE HELP ME PLEASE.

[Paul]

The 'Locked' status of any cells should not be affected by the code. While it's true that the Main Sheet is unprotected at the beginning of the macro, allowing any cell to be edited, it is very quickly re-protected. So unless you have users that can edit cells in a fraction of a second, you shouldn't have anything to worry about.

Since you're working with a specific sheet, rather than using 'ActiveSheet', use:

Sheets("Main Sheet").Unprotect Password:="007"

and

Sheets("Main Sheet").Protect Password:="007"

[ss_bb_24]

Excellent!!! thanks a lot Pjoaquin