Running Macros across protected sheets.

[Excelme at work]

Hi there,

I recently created a calendar based spreadsheet with a drop down menu which lists, calendar months, and hides columns as necessary. Works perfectly.

However, I now need to share this document around so have protected all worksheets within, the problem then arises that if you select a month, you get the error message

Run time error 1004
Unable to set the hidden property of the range class.

The macro for the calendar is as follows;

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And Target.Row = 1 Then
Dim c As Range, rng
Dim mymonth, month_val
month_val = Range("d1").Value


mymonth = Month(month_val)


Set rng = Range("e3:NJ3")
For Each c In rng
If Month(c) = mymonth Then
c.EntireColumn.Hidden = False
Else
c.EntireColumn.Hidden = True
End If
Next c
End If




End Sub

Would anyone happen to have a suitable workaround?
I have unticked "locked" on the entire column range that could potentially be hidden.
As soon as I unprotect the worksheet, the drop down list functions as expected.

 

[Joe4]

You will need to add VBA code to your procedures that unprotect your sheet, make the changes, then re-protect the sheet.

See: https://analysistabs.com/excel-vba/protect-unprotect-worksheets/

 

[Excelme at work]

You will need to add VBA code to your procedures that unprotect your sheet, make the changes, then re-protect the sheet.

See: https://analysistabs.com/excel-vba/protect-unprotect-worksheets/

Thanks I had hoped for an alternative.
I already have macros to protect / unprotect all worksheets will have to adapt to just this single sheet. Concerned it may cause the function to become unnecessarily slow though.

 

[Joe4]

Thanks I had hoped for an alternative.

You don't really need to add extra macros/procedures.
You should be able to just add the lines to unprotect/protect to your current code.
Those lines of code should have negligible effect on your code's performance (as long as you do not put them inside the loop so the run hundreds of times).

 

[Excelme at work]

You don't really need to add extra macros/procedures.
You should be able to just add the lines to unprotect/protect to your current code.
Those lines of code should have negligible effect on your code's performance (as long as you do not put them inside the loop so the run hundreds of times).

Hi thanks, could you explain more, I am more at a loss as to where the code should go, how it would be coded to only protect / unprotect one sheet?

I am relatively new to VBA this being the first time I have used more than one function simultaneously, learning as I go.

 

[Joe4]

Something like this (note that I also cleaned up your code a little bit):

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim c As Range, rng As Range
    Dim mymonth As Integer
    
    If Target.Column = 4 And Target.Row = 1 Then
[COLOR=#ff0000]        ActiveSheet.Unprotect "password"[/COLOR]
        mymonth = Month(Range("D1").Value)
        Set rng = Range("E3:NJ3")
        For Each c In rng
            If Month(c) = mymonth Then
                c.EntireColumn.Hidden = False
            Else
                c.EntireColumn.Hidden = True
            End If
        Next c
[COLOR=#ff0000]        ActiveSheet.Protect "password", True, True[/COLOR]
    End If

End Sub