Insert Picture

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

I think I need to rephrase the question - 22 Oct 2007 08:33 GMT

I am using the following Macro to insert a picture into a protected sheet

Sub testme()
Dim myPictName As Variant
Dim myPict As Picture
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

myPictName _
= Application.GetOpenFilename("Picture files, *.bmp;*.jpg;*.gif")
If myPictName = False Then
MsgBox "try later!"
Exit Sub
End If

With wks
.Unprotect Password:="hi"
With .Range("a1:B9")
Set myPict = .Parent.Pictures.Insert(myPictName)
myPict.Top = .Top
myPict.Left = .Left
myPict.Width = .Width
myPict.Height = .Height
End With
.Protect Password:="hi"
End With

End Sub

The macro resizes a picture to fit the cell range

Is it possible to fit a picture into the cell range and keep its
proportions? (ie make it fit into the cell range and relatively keep its
normal height and width)

Additionally, can you tell the macro to enable "Edit Object" in tools -
protection - sheet protect prior to Password Protecting again?

Reply to this Message

Bob Phillips - 22 Oct 2007 09:46 GMT

Sub testme()
Dim myPictName As Variant
Dim myPict As Picture
Dim wks As Worksheet
Dim nScaleWide As Double
Dim nScaleHigh As Double
Dim nScale As Double

Set wks = Worksheets("sheet1")

myPictName _
= Application.GetOpenFilename("Picture files, *.bmp;*.jpg;*.gif")
If myPictName = False Then
MsgBox "try later!"
Exit Sub
End If

With wks
.Unprotect Password:="hi"
With .Range("a1:B9")
Set myPict = .Parent.Pictures.Insert(myPictName)
nScaleWide = myPict.Width / .Width
nScaleHigh = myPict.Height / .Height
nScale = IIf(nScaleWide < nScaleHigh, nScaleHigh, nScaleWide)
myPict.Top = .Top
myPict.Left = .Left
myPict.Width = myPict.Width / nScale
myPict.Height = myPict.Height / nScale
End With
'.Protect Password:="hi"
End With

End Sub

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I am using the following Macro to insert a picture into a protected sheet
>
[quoted text clipped - 34 lines]
> Additionally, can you tell the macro to enable "Edit Object" in tools -
> protection - sheet protect prior to Password Protecting again?

Reply to this Message

I think I need to rephrase the question - 23 Oct 2007 00:14 GMT

Thanks Bob. That works great.

Is there any way that the picture can be centred vertically in the range of
cells instead of at the top?

> Sub testme()
> Dim myPictName As Variant
[quoted text clipped - 68 lines]
> > Additionally, can you tell the macro to enable "Edit Object" in tools -
> > protection - sheet protect prior to Password Protecting again?

Reply to this Message

Bob Phillips - 23 Oct 2007 10:02 GMT

Try this, It centres vertically and horizontally in case it scales long as
against wide.

Sub testme()
Dim myPictName As Variant
Dim myPict As Picture
Dim wks As Worksheet
Dim nScaleWide As Double
Dim nScaleHigh As Double
Dim nScale As Double
Dim nWidth As Double
Dim nHeight As Double

Set wks = Worksheets("sheet1")

myPictName _
= Application.GetOpenFilename("Picture files, *.bmp;*.jpg;*.gif")
If myPictName = False Then
MsgBox "try later!"
Exit Sub
End If

With wks
.Unprotect Password:="hi"
With .Range("a1:B9")
Set myPict = .Parent.Pictures.Insert(myPictName)
nScaleWide = myPict.Width / .Width
nScaleHigh = myPict.Height / .Height
nScale = IIf(nScaleWide < nScaleHigh, nScaleHigh, nScaleWide)
nWidth = myPict.Width / nScale
nHeight = myPict.Height / nScale
myPict.Width = nWidth
myPict.Height = nHeight
myPict.Top = (.Height - nHeight) / 2
myPict.Left = (.Width - nWidth) / 2
End With
.Protect Password:="hi"
End With

End Sub

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Thanks Bob. That works great.
>
[quoted text clipped - 78 lines]
>> > Additionally, can you tell the macro to enable "Edit Object" in tools -
>> > protection - sheet protect prior to Password Protecting again?

Reply to this Message

I think I need to rephrase the question - 23 Oct 2007 12:11 GMT

Hi Bob,

Thanks for your help on this macro

I tried it. For some reason it moves the picture directly above and outside
of the preset (a1:b9) range

> Try this, It centres vertically and horizontally in case it scales long as
> against wide.
[quoted text clipped - 119 lines]
> >> > Additionally, can you tell the macro to enable "Edit Object" in tools -
> >> > protection - sheet protect prior to Password Protecting again?

Reply to this Message

Bob Phillips - 23 Oct 2007 15:03 GMT

Really, it worked for me. Just tried it again and works every time.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi Bob,
>
[quoted text clipped - 132 lines]
>> >> > tools -
>> >> > protection - sheet protect prior to Password Protecting again?

Reply to this Message

I think I need to rephrase the question - 24 Oct 2007 07:08 GMT

It must be the way I have the sheet set up. I will have to check it over to
see what I have done. Thanks for your help

> Really, it worked for me. Just tried it again and works every time.
>
[quoted text clipped - 134 lines]
> >> >> > tools -
> >> >> > protection - sheet protect prior to Password Protecting again?

Reply to this Message

Rate this thread: