run-time error '1004' when sheet is protected

[cybrkada]

Can you tell me what's wrong with my codes? I have macros on my other protected sheets and they are working fine. I have summary sheet named Index positioned at the very last and that's where I am getting the error whenever I protect it. Here are the codes:

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim n As Integer
Dim calcState As Long, scrUpdateState As Long


calcState = Application.Calculation
Application.Calculation = xlCalculationManual
scrUpdateState = Application.ScreenUpdating
Application.ScreenUpdating = False


n = 1


With Me
.Columns(1).ClearContents
.Cells(1, 1) = "Date"
.Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
n = n + 1
With wSheet
.Range("B1").Name = "Start_" & wSheet.Index
.Hyperlinks.Add anchor:=.Range("B1"), Address:="", _
SubAddress:="Index", TextToDisplay:="Back to Index"
End With

Me.Hyperlinks.Add anchor:=Me.Cells(n, 1), Address:="", _
SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet

Application.Calculation = calcState
Application.ScreenUpdating = scrUpdateState
End Sub

Whenever I debug the codes, the line .Columns(1).ClearContents is being highlighted. Did I miss anything?

Thanks,

 

[dmt32]

If contents of your worksheet are protected you need to unprotect them before carrying the actions.

Try adding this change & see if helps. you will need to insert password if required where shown.

With Me
        .Unprotect Password:="passwordhere"
        .Columns(1).ClearContents
        .Cells(1, 1) = "Date"
        .Cells(1, 1).Name = "Index"
    End With

Dave

 

[cybrkada]

Hi Dave,

Thanks for your reply but I don't want to unprotect the sheet, the reason I am doing it is to prevent users from accidentally deleting cells and seeing the formulas. However I am getting the runtime error everytime the sheet gets protected.

Thanks,

 

[dmt32]

Hi Dave,

Thanks for your reply but I don't want to unprotect the sheet, the reason I am doing it is to prevent users from accidentally deleting cells and seeing the formulas. However I am getting the runtime error everytime the sheet gets protected.

Thanks,

Just re-protect once code action is complete:

With Me
        .Unprotect Password:="passwordhere"
        .Columns(1).ClearContents
        .Cells(1, 1) = "Date"
        .Cells(1, 1).Name = "Index"
        .Protect Password:="passwordhere"
    End With

 

[cybrkada]

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim n As Integer
Dim calcState As Long, scrUpdateState As Long

calcState = Application.Calculation
Application.Calculation = xlCalculationManual
scrUpdateState = Application.ScreenUpdating
Application.ScreenUpdating = False

n = 1

With Me
.Unprotect Password:="Secret"
.Columns(1).ClearContents
.Cells(1, 1) = "Date"
.Cells(1, 1).Name = "Index"
.Protect Password:="Secret"
End With
For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
n = n + 1
With wSheet
.Range("B1").Name = "Start_" & wSheet.Index
.Hyperlinks.Add anchor:=.Range("B1"), Address:="", _
SubAddress:="Index", TextToDisplay:="Back to Index"
End With
Me.Hyperlinks.Add anchor:=Me.Cells(n, 1), Address:="", _
SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet
[COLOR=#0000ff][B]Application.Calculation = calcState
[/B][/COLOR]Application.ScreenUpdating = scrUpdateState
End Sub

Hi, I am still getting the same error but it is highlighting a different line when I debug it! Also the macro does not work anymore. Please help!