Unprotect a Worksheet in VB code. Excel 2007

[Bongo68]

Hi

I have some code that hides rows in a spreadsheet but it doesn't work when I protect the worksheet. Can anybody tell me the code to unprotect the sheet before it runs the hide rows bit and then protect the sheet at the end of the code. Sorry I know this is probably really simple but I've only just started to write macros again after a loooooooooong break.

Many thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

Set KeyCells = Range("REF_TYPE")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then


With Sheets("Referral Form")
If Range("REF_TYPE_LKP").Value = 0 Then Rows("6:70").EntireRow.Hidden = True
If Range("REF_TYPE_LKP").Value = 1 Then Rows("6:70").EntireRow.Hidden = False
If Range("REF_TYPE_LKP").Value = 1 Then Rows("29:35").EntireRow.Hidden = True
If Range("REF_TYPE_LKP").Value = 2 Then Rows("6:70").EntireRow.Hidden = False
If Range("REF_TYPE_LKP").Value = 2 Then Rows("29:35").EntireRow.Hidden = False
End With
End If
End Sub

[judgeh59]

Hello and welcome to the forum....Moderators will probably tell you soon to put code brackets around code....See Rules to learn how....so, beyond that, try this...

    ActiveSheet.Unprotect

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

So one unprotects, one protects......by the way, I figured this out by using record macro....a great way to start....

[judgeh59]

did this help?

[Bongo68]

Hi. Sorry for the delay not had chance to try before now.

It works a treat thank you. I probably should have said I have a password that I need to incorporate into the code. I've been trying to do that bit but not quite there yet.

Thanks for your help.