CODEWITHSUNDEEP
vbaexcel
flowers1234
flowers1234

Reputation: 347

unlock specific area in a protected excel sheet with vba

I have to unlock a specific range (D6:BC116) in a excel sheet. It should be able for other people editing this specific area. So it should be unlocked for them. At first I manually protect the whole sheet without any code. And after that I want to unprotect the specific area for editing. But something always goes wrong. I have these two codes. The first code has the hidden property it only hides empty cells. The other code I am trying to unprotect specific area I want to edit after protecting the whole sheet. I am not really sure if the problem is in the first code because of the hidden property? And I am not sure if they are in a relation?

Private Sub Worksheet_Change(ByVal Target As Range)
 For Each cell In Range("B6:B112")
 If cell.Value <> "" Then
 cell.EntireRow.Hidden = False
 Else
 cell.EntireRow.Hidden = True
 End If
 Next cell
End Sub

Sub UnlockCells()

 Worksheets("Sheet1").Range("D6:BC116").Locked = False
 Worksheets("Sheet1").Protect

End Sub

And when I execute this I always get "Index out of range"

Thanks!

Upvotes: 0

Views: 4122

Answers (1)

Vityata
Vityata

Reputation: 43575

I think you need to unprotect before unlocking. Like this:

 With Worksheets("Sheet1")
 .Unprotect "MyLongAndSecurePassword"
 .Range("D6:BC116").Locked = False
 .Protect
 End with

Concerning the first part of the code - make sure that you use a variable, which is not named cell, because cell is used by the VBEditor. Name your variable rngCell, myCell or anything else but cell. And declare it like this: Dim rngCell as Range.

Last point - lock your worksheet and try to hide and unhide manually the rows. Is it possible? If not, you know the reason for the error.

Edit:

To check whether the sheet is protected, try this in the Worksheet_Change:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Worksheets("Sheet1").ProtectContents Then Exit Sub

    For Each cell In Range("B6:B112")
        If cell.Value <> "" Then
            cell.EntireRow.Hidden = False
        Else
            cell.EntireRow.Hidden = True
        End If
    Next cell

End Sub

Upvotes: 2

Related Questions