Can DrawingObjects:=False be applied to a range instead of the entire worksheet?

**Royzer** · 07-11-2017, 06:07 PM

The files in the outlined area need to be protected except when documents need to be added or deleted, so I have a Lock/Unlock button for protecting the cells in that range.

This code snippet is as close as I have been able to come to allowing .pdf files to be inserted, moved around and deleted from the outlined range (B26:C37) while the sheet is protected. The problem is that there are a couple of show/hide pictures outside of the that range that toggle red (locked) or green (unlocked) based on whether the range is locked from editing or not, and the snippet also unprotects them. I'd like them to stay protected with the rest of the sheet. Is there a way to apply this to just B26:C37?

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

**thatandyward** · 07-11-2017, 10:22 PM

you could take this approach

Sub UnlockDrawingObjects()

    Dim sh As Shape
    Dim shRng As Range
    Dim OutlinedArea As Range

With Sheet1

    'turn on protection
    .Protect , DrawingObjects:=True, Contents:=True, Scenarios:=True

    'define OutlinedArea range
    Set OutlinedArea = .Cells(26, 2).Resize(13, 2) 'B26:C38

    'loop all shapes in worksheet
    For Each sh In .Shapes
    
        With sh
            
            'reset to locked
            .Locked = True

            'set temp range of cells shape object occupies
            Set shRng = Range(.TopLeftCell.Address & ":" & .BottomRightCell.Address)
            
            'test if temp range intersects with outlined area, unlock shape if it does
            If Not Intersect(OutlinedArea, shRng) Is Nothing Then .Locked = False
        
        End With
    
    Next sh

End With

End Sub

set the protection with DrawingObjects:=True then loop all Shapes in the Worksheet, test if they intersect with the OutlinedArea. if so then unlock them so the items inside the OutlinedArea remain unlocked, whilst anything outside the OutlinedArea is locked.

it's not ideal and a bit of a hack, but hopefully it will achieve what you want.

**Royzer** · 07-12-2017, 10:27 AM

I changed "Sheet1" to the actual sheet name.

It crashes at the yellow point in the code and gives this message:

Run-time error '1004'

Application-defined or object-defined error

**thatandyward** · 07-12-2017, 11:21 AM

I didn't really mean for the code to be run as a solution to your problem, more as an illustration of a possible approach you could take with respect to determining a shapes position relative to a defined range.

I don't know the specifics of what shape objects you have on your sheet, I suspect the error is caused by trying to apply the .locked property to a shape type that can't accept it - although I'm not that familiar with all the different flavors of shape objects so not really sure.

I would suggest testing the shape type, either with a simple IF…THEN statement or, if you have multiple shapes, a Select Case statement, and then adding the relevant code depending on the shape type and what you want to do

**Royzer** · 07-12-2017, 12:19 PM

Sorry, I misunderstood.

Thanks. I'll try what you suggested.

Can DrawingObjects:=False be applied to a range instead of the entire worksheet?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Can DrawingObjects:=False be applied to a range instead of the entire worksheet?

Re: Can DrawingObjects:=False be applied to a range instead of the entire worksheet?

Re: Can DrawingObjects:=False be applied to a range instead of the entire worksheet?

Re: Can DrawingObjects:=False be applied to a range instead of the entire worksheet?

Re: Can DrawingObjects:=False be applied to a range instead of the entire worksheet?

Thread Information

Users Browsing this Thread

Similar Threads

Specifing entire worksheet as range to export sheet to access

[SOLVED] Macro DrawingObjects=false, but cannot insert a picture?

Conditional format using text applied to entire row

Table Array Entire Worksheet Instead Of Named Range

Address Entire Worksheet as a range

ScreenUpdating just a range of cells instead of entire worksheet

Macro to print a selected range, not entire worksheet

Bookmarks

Bookmarks

Posting Permissions