Run-time error 1004

[pbnadia]

Hello,

I've created a calendar with macros, however when I try to protect the sheet I get a runtime error. I'm learning macros and have tried various codes but have not helped. Here is the macro I have:

Sub HideMonths()
Application.ScreenUpdating = False
Dim rngDates As Range, rngMonths As Range, MyCell As Range
Dim strMonth As String, strYear As String, strMonthYear As String
Set rngDates = [Dates].Cells ' individual dates
Set rngMonths = [Months].Cells ' drop-down list
Set rngYears = [Years].Cells ' drop down list
strMonth = WorksheetFunction.Index(rngMonths, Range("C1"))
strYear = WorksheetFunction.Index(rngYears, Range("C2"))
strMonthYear = Format(DateValue(strMonth & " " & strYear), "mmm yyyy")
For Each MyCell In rngDates
    If Format(MyCell, "mmm yyyy") < strMonthYear _
        Or Format(MyCell, "mmm yyyy") > strMonthYear Then
        Columns(MyCell.Column).Hidden = True
    Else
        Columns(MyCell.Column).Hidden = False
        
    End If
Next MyCell
Application.ScreenUpdating = True
End Sub

Here is the error I when I protect the sheet:

Run -time error '1004':
"Unable to set the Hidden property of the Range class"

Hopefully someone can help!

Thanks in advance!

 

[Jonmo1]

You have to UNprotect the sheet first
Then hide/unhide columns
Then ReProtect the sheet..

Something like

Sheets("SheetName").UnProtect "PasswordGoesHere"
For Each MyCell In rngDates
    If Format(MyCell, "mmm yyyy") < strMonthYear _
        Or Format(MyCell, "mmm yyyy") > strMonthYear Then
        Columns(MyCell.Column).Hidden = True
    Else
        Columns(MyCell.Column).Hidden = False

    End If
Next MyCell
Sheets("SheetName").Protect "PasswordGoesHere"

 

[pbnadia]

Hello Jonmo1,

Thank you for your quick response. I added the code above my code a and get "Compile error: Invalid outside procedure" where the "password" goes. I do enter my password there correct?
this is what I have:

Sheets("2015").Unprotect "passwordgoeshere"
For Each MyCell In rngDates
If Format(MyCell, "mmm yyyy") < strMonthYear _
Or Format(MyCell, "mmm yyyy") > strMonthYear Then
Columns(MyCell.Column).Hidden = True
Else
Columns(MyCell.Column).Hidden = False
End If
Next MyCell
Sheets("2015").Protect "Passwordgoeshere"
Sub HideMonths()
Application.ScreenUpdating = False
Dim rngDates As Range, rngMonths As Range, MyCell As Range
Dim strMonth As String, strYear As String, strMonthYear As String
Set rngDates = [Dates].Cells ' individual dates
Set rngMonths = [Months].Cells ' drop-down list
Set rngYears = [Years].Cells ' drop down list
strMonth = WorksheetFunction.Index(rngMonths, Range("C1"))
strYear = WorksheetFunction.Index(rngYears, Range("C2"))
strMonthYear = Format(DateValue(strMonth & " " & strYear), "mmm yyyy")
For Each MyCell In rngDates
If Format(MyCell, "mmm yyyy") < strMonthYear _
Or Format(MyCell, "mmm yyyy") > strMonthYear Then
Columns(MyCell.Column).Hidden = True
Else
Columns(MyCell.Column).Hidden = False

End If
Next MyCell
Application.ScreenUpdating = True
End Sub

 

[Jonmo1]

Looks like you copied my whole code and pasted it into your module above the beginning of your original code.

You need to put the UnProtect and Protect lines INSIDE your orginal code.

 

[pbnadia]

Sorry, could you tell me where exactly? I'm new and not sure where to apply it.

 

[Jonmo1]

Try

Sub HideMonths()
 Application.ScreenUpdating = False
 Dim rngDates As Range, rngMonths As Range, MyCell As Range
 Dim strMonth As String, strYear As String, strMonthYear As String
 Set rngDates = [Dates].Cells ' individual dates
 Set rngMonths = [Months].Cells ' drop-down list
 Set rngYears = [Years].Cells ' drop down list
 strMonth = WorksheetFunction.Index(rngMonths, Range("C1"))
 strYear = WorksheetFunction.Index(rngYears, Range("C2"))
 strMonthYear = Format(DateValue(strMonth & " " & strYear), "mmm yyyy")
Sheets("SheetName").UnProtect "PasswordGoesHere"
 For Each MyCell In rngDates
 If Format(MyCell, "mmm yyyy") < strMonthYear _
 Or Format(MyCell, "mmm yyyy") > strMonthYear Then
 Columns(MyCell.Column).Hidden = True
 Else
 Columns(MyCell.Column).Hidden = False

 End If
 Next MyCell
Sheets("SheetName").Protect "PasswordGoesHere"
 Application.ScreenUpdating = True
 End Sub

make sure you actually put in the valid password where I highlighted in red.

 

[pbnadia]

Thanks!!!! you are awesome! that totally worked!

 

[Jonmo1]

You're welcome.

 

[pbnadia]

Thank you again for helping me last time.. I have another little problem...

I have drop down lists from cell ranges I9:NI39 and need to be able to drag on them. I already formatted the cell and unchecked the Hidden and locked boxes, however when I protect the sheet I cannot drag on those cells. Is there a Macro I can add to the above one to help with this problem?


Thanks again in advance!