unprotect range vba

[Kaps_mr2]

Dear All,

I am trying to unprotect certain parts of a workbook. I would have thought that the following would work but no luck :-

I get "object does not support this property or method":-

Invoicing_Tool.Sheets("Job List").Protect


'Big jobs

For j = 5 To 19

If Len(Invoicing_Tool.Sheets("Job List").Range("B" & j).Value) = 0 Then
'unprotect ranges

Set range_to_unprotect = Invoicing_Tool.Sheets("Job List").Range("B" & j & ":F" & j)

range_to_unprotect.Unprotect

Set range_to_unprotect = Invoicing_Tool.Sheets("Job List").Range("H" & j)

range_to_unprotect.Unprotect

Set range_to_unprotect = Invoicing_Tool.Sheets("Job List").Range("j" & j * ":W" & j)

range_to_unprotect.Unprotect



End If


Next j

 

[mvptomlinson]

You don't protect/unprotect ranges, you protect/unprotect worksheets or workbooks.

 

[Jonmo1]

Protection is applied to the SHEET, not a range within the Sheet..

So you have to unprotect the sheet.

Sheets("Job List").Unprotect

Hope that helps..

 

[lenze]

I think you need to use UNLOCK/LOCK
You protect WorkSheets, You Lock and UnLock cells. Note the WorkSheet must be Unprotected to change the Locked Property of a cell

lenze

 

[Kaps_mr2]

I have tried the Locked cells method (I presume it can apply to a range) but I can still edit the cell contents - which I don't want. Here is my code - any suggestions :-

Public Sub Protect_Sheet()
Dim j As Long
Dim range_to_protect As Range


Invoicing_Tool.Sheets("Job List").Unprotect


'Big jobs


For j = 5 To 19

        If Len(Invoicing_Tool.Sheets("Job List").Range("B" & j).Value) > 0 Then
        'unprotect ranges
        
            Set range_to_protect = Invoicing_Tool.Sheets("Job List").Range("B" & j & ":F" & j)
            
            range_to_protect.Locked = True
            
            Set range_to_protect = Invoicing_Tool.Sheets("Job List").Range("H" & j)
            
            range_to_protect.Locked = True
            
            Set range_to_protect = Invoicing_Tool.Sheets("Job List").Range("j" & j & ":W" & j)
            
            range_to_protect.Locked = True
            
            
        
        End If
        
        
Next j

MsgBox "here"

End Sub

thanks

Kaps

 

[VoG]

You need to re-protect the sheet (line in red)

Public Sub Protect_Sheet()
Dim j As Long
Dim range_to_protect As Range


Invoicing_Tool.Sheets("Job List").Unprotect


'Big jobs


For j = 5 To 19

        If Len(Invoicing_Tool.Sheets("Job List").Range("B" & j).Value) > 0 Then
        'unprotect ranges
        
            Set range_to_protect = Invoicing_Tool.Sheets("Job List").Range("B" & j & ":F" & j)
            
            range_to_protect.Locked = True
            
            Set range_to_protect = Invoicing_Tool.Sheets("Job List").Range("H" & j)
            
            range_to_protect.Locked = True
            
            Set range_to_protect = Invoicing_Tool.Sheets("Job List").Range("j" & j & ":W" & j)
            
            range_to_protect.Locked = True
            
            
        
        End If
        
        
Next j

Invoicing_Tool.Sheets("Job List").Protect
MsgBox "here"

End Sub

 

[Kaps_mr2]

Peter,

Thanks - I tried this but now the whole sheet is protected rather certain ranges.

Kaps

 

[VoG]

Try adding the line in red

For j = 5 To 19

        If Len(Invoicing_Tool.Sheets("Job List").Range("B" & j).Value) > 0 Then
        'unprotect ranges
            Invoicing_Tool.Sheets("Job List").UsedRange.Locked = False
            
            Set range_to_protect = Invoicing_Tool.Sheets("Job List").Range("B" & j & ":F" & j)

 

[Kaps_mr2]

again I've tried that and it doesn't do what I expect. Certain parts are protected but not the bits I want.My code is now :-

thanks

Kaps

Public Sub Protect_Sheet()
Dim j As Long
Dim range_to_protect As Range


Invoicing_Tool.Sheets("Job List").Unprotect


'Big jobs


For j = 5 To 19

        If Len(Invoicing_Tool.Sheets("Job List").Range("B" & j).Value) > 0 Then
        'unprotect ranges
        
        Invoicing_Tool.Sheets("Job List").UsedRange.Locked = False

            Set range_to_protect = Invoicing_Tool.Sheets("Job List").Range("B" & j & ":F" & j)
            
            range_to_protect.Locked = True
            
            Set range_to_protect = Invoicing_Tool.Sheets("Job List").Range("H" & j)
            
            range_to_protect.Locked = True
            
            Set range_to_protect = Invoicing_Tool.Sheets("Job List").Range("j" & j & ":W" & j)
            
            range_to_protect.Locked = True
            
            
        
        End If
        
        
Next j


Invoicing_Tool.Sheets("Job List").Protect

Thanks

Kaps

 

[VoG]

Maybe the unlocking needs to come out of the loop:

Public Sub Protect_Sheet()
Dim j As Long
Dim range_to_protect As Range


Invoicing_Tool.Sheets("Job List").Unprotect

Invoicing_Tool.Sheets("Job List").UsedRange.Locked = False

'Big jobs


For j = 5 To 19

        If Len(Invoicing_Tool.Sheets("Job List").Range("B" & j).Value) > 0 Then
        'unprotect ranges
            
            Set range_to_protect = Invoicing_Tool.Sheets("Job List").Range("B" & j & ":F" & j)
            
            range_to_protect.Locked = True
            
            Set range_to_protect = Invoicing_Tool.Sheets("Job List").Range("H" & j)
            
            range_to_protect.Locked = True
            
            Set range_to_protect = Invoicing_Tool.Sheets("Job List").Range("j" & j & ":W" & j)
            
            range_to_protect.Locked = True
            
            
        
        End If
        
        
Next j

Invoicing_Tool.Sheets("Job List").Protect
MsgBox "here"

End Sub