allow template user to insert picture

[Guest]

I'm creating a form which I will save as a protected template. How do I allow
users to insert their own logo in a designated area? Is this possible? I
tried using the "image" control box field but I'm not sure if this is what it
is intended for.

Thanks

 

[Dave Peterson]

I think I'd use a macro that asked for the logo, unprotected the worksheet,
added the logo and then reprotected the worksheet.

Kind of...

Option Explicit
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

And you could completely control where the logo went.

 

[Guest]

Dave,

That works great! Now, since I don't know ahead of time how big or small the
logos could be, if I tell the macro to fit it within a certain range, it
could get stretched out of proportion. I tried just referencing one cell as
the starting point, but instead it squeezed or stretched the picture into
that one cell.

 

[Dave Peterson]

Try commenting out these lines:
myPict.Width = .Width
myPict.Height = .Height

The logos will be their "natural" size.