Unable to set the Hidden property of the Range Class

[manc]

Dear Demigods,

I am using the following VBA:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Address(0, 0) = "B14" Then
    Cancel = True
    Rows("15:38").Hidden = Not Rows("15:38").Hidden
End If


End Sub

The codes works well until i come to protecting the worksheet.
I leave B14 unLocked (so the code still works), and i also leave some cells within 15:38 unLocked (for example C16, Q16, T37 etc).

When i then protect the worksheet, i get the run-time 1004 error together with
"Unable to set the Hidden property of the Range Class"

Any ideas please?

Best regards
manc

 

[Fluff]

You need to unprotect the sheet at the start of the code & reprotect it again at the end.

 

[manc]

Thank-you Fluff.
Advice greatly appreciated and problem resolved.

Many thanks
manc

 

[Fluff]

You're welcome & thanks for the feedback

 

[robertjude]

Hi. Been experiencing the same problem. Just new to using excel VBA to i don't know exactly how to protect and unprotect again the sheet.

I tried using -- Sheets("Sheet1").Protect -- but i didn't work. I don't even know where should I put it. Help would be much appreciated. TIA.

 

[MARK858]

Did you include the password in the code if you set one for the sheet?

 

[robertjude]

Did you include the password in the code if you set one for the sheet?

I didnt include any. Anyways, I encountered this error when using a dropdown menu (rows are getting hidden depends on that dropdown value). Instead of the dropdown, i tried using just checkboxes and I think it did good. I'm just gonna settle for this for the meantime. Thanks for your response though.