Protect Worksheet but allow re-sizing of objects

[Allilue]

Last one, I promise!!

If want to be able to resize a picture that I've pasted into a textbox while my worksheet is protected, is there a specific "AllowObject....=True" term for that? I've looked in the Help and cannot figure out which one I need to use.

Thanks,
Allison

 

[SkipVought]

Hi,

You are restriced by the sheet protect properties.

"...want to be able to resize a picture that I've pasted into a textbox while my worksheet is protected..."

Please explain, functionally, what the requirement is.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[SkipVought]

sorry, ...restricted...

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[SkipVought]

Just out of curiosity, how did you paste a pic in a textbox?

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[Allilue]

What I mean with the textbox is that I copy a range from within the workbook, and then paste this by selecting a textbox first. The reason I mentioned a picture is because when i right-click the pasted range, the properties state that this is a picture.

So the requirement is:

Since the worksheet is already protected (I unprotect it before i do the select texbox), how do I re-protect it and allow a user to be able to re-size the pasted picture?

Sorry I'm not that good with this and am probably using all the wrong terms... :-(

Thanks,
Allison

 

[SkipVought]

Please show the code where you copy and paste into a textbox.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[Allilue]

Here it is,

Range("B4:AR107").Select
Selection.Copy
Sheets("Email").Select
Sheet11.Shapes("Text Box 20").Select
ActiveSheet.Paste
Selection.ShapeRange.IncrementLeft 16.5
Selection.ShapeRange.IncrementTop 69.75
Selection.ShapeRange.ScaleWidth 0.95, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.95, msoFalse, msoScaleFromTopLeft

 

[SkipVought]

I am TOTALLY confused by the difference between what you are SAYING and what your code is DOING.

I just ran your code and I get a picture object on the sheet in addition to the textbox. Nothing gets copied INTO the textbox!

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[Allilue]

When I don't select the textbox, then it doesn't work properly. I need to select the textbox first, and then it pastes as a picture, although you're correct that it doesn't actually copy INTO it.

So what I want to unprotect is the picture to re-size.

I'm confused as well... but sorry to confuse you too!

 

[SkipVought]

I changed the sheet and range references, but the code works...

Sub test()
    Sheet2.Range("a1:c8").CopyPicture
    Sheet1.Paste Sheet1.[d5]
    With Sheet1.Shapes(Sheet1.Shapes.Count) 'this is the LAST shape added to the sheet
        .IncrementLeft 16.5
        .IncrementTop 69.75
        .ScaleWidth 0.95, msoFalse, msoScaleFromTopLeft
        .ScaleHeight 0.95, msoFalse, msoScaleFromTopLeft
    End With

End Sub

Are you not shaping it to fit in a cell?

Sub test()
    Sheet2.Range("a1:c8").CopyPicture
    Sheet1.Unprotect
    Sheet1.Paste Sheet1.[d5]
    With Sheet1.Shapes(Sheet1.Shapes.Count) 'this is the LAST shape added to the sheet
        .Left = .TopLeftCell.Left
        .Top = .TopLeftCell.Top
        .Width = .TopLeftCell.Width
        .Height = .TopLeftCell.Height
    End With
    Sheet1.Protect
End Sub

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[Allilue]

Thanks. So in terms of re-protecting the sheet and allowing the shape to be re-sized (the shape being the picture and not necessarily the textbox as you rightly mentioned that the picture object is not in the textbox), can this be done now?

 

[SkipVought]

Is that not what the code is doing, sans password et al???

I don't understand the question.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]