inserting pictures

[Denny]

Is it possible to insert pictures into each sheet of a workbook that will
have the same size and be in the same location each time? TIA!

 

[Dave Peterson]

Yeah....

But I think you'll have to explain more about what you really mean.

Do you want a picture (like a logo) added to E1:G2 for each worksheet?

Or maybe you want a picuture added to each worksheet that is always in the same
position--but maybe not over the same cells?

If over the same cells:

Option Explicit
Sub testme02()

Dim myPictureName As Variant
Dim myPict As Picture
Dim myRng As Range
Dim myAddr As String
Dim wks As Worksheet

myPictureName = Application.GetOpenFilename _
(filefilter:="Picture Files,*.jpg;*.bmp;*.tif;*.gif")

If myPictureName = False Then
Exit Sub 'user hit cancel
End If

myAddr = "e1:g2"

For Each wks In ActiveWorkbook.Worksheets
Set myRng = wks.Range(myAddr)
Set myPict = wks.Pictures.Insert(myPictureName)
myPict.Top = myRng.Top
myPict.Width = myRng.Width
myPict.Height = myRng.Height
myPict.Left = myRng.Left
myPict.Placement = xlMoveAndSize
Next wks
End Sub


If in the same position (maybe not over the same cells):

Option Explicit
Sub testme02()

Dim myPictureName As Variant
Dim myPict As Picture
Dim myRng As Range
Dim wks As Worksheet

myPictureName = Application.GetOpenFilename _
(filefilter:="Picture Files,*.jpg;*.bmp;*.tif;*.gif")

If myPictureName = False Then
Exit Sub 'user hit cancel
End If

With Worksheets("sheet1")
Set myRng = .Range("c3:e5")
End With

For Each wks In ActiveWorkbook.Worksheets
Set myPict = wks.Pictures.Insert(myPictureName)
myPict.Top = myRng.Top
myPict.Width = myRng.Width
myPict.Height = myRng.Height
myPict.Left = myRng.Left
myPict.Placement = xlMoveAndSize
Next wks
End Sub

 

[Denny]

Thanks Dave,
It is the first example. I will give this a try.

 

[Denny]

Hey Dave,
This seem to work great until I noticed that it had placed a picture on each
worksheet in the location stated. What I want is a picture to be place on a
work sheet in say the range is m1: p4.
I want a different picture on each worksheet of the workbook. I will have
25 worksheets and pictures to place. How can I stop the program from
placing a picture on each worksheet? TIA! Thanks again for the help. This
is a great start.

Thanks Dave,
It is the first example. I will give this a try.

 

[Dave Peterson]

How do you know what picture to place on what worksheet?

Can you create a table on a helper worksheet:

Column A: Picture Name (include the path here or in code???)
Column B: Sheet Name
column C: M14 (or the range you want:

Row 1 will have headers (just to help you if you need a reminder).

Option Explicit
Sub testme02()

Dim IndexWks As Worksheet
Dim testStr As String
Dim testWks As Worksheet
Dim testRng As Range

Dim myPictureName As Variant
Dim myPict As Picture
Dim myRng As Range

Dim myCell As Range
Dim errMsg As String

Set IndexWks = Worksheets("Index")
With IndexWks
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
testStr = ""
On Error Resume Next
testStr = Dir(myCell.Value)
On Error GoTo 0

Set testWks = Nothing
On Error Resume Next
Set testWks = Worksheets(myCell.Offset(0, 1).Value)
On Error GoTo 0

Set testRng = Nothing
On Error Resume Next
Set testRng = testWks.Range(myCell.Offset(0, 2).Value)
On Error GoTo 0

errMsg = ""
If testStr = "" Then
errMsg = vbLf & "Error on " & myCell.Row & "--picture not found!"
End If

If testWks Is Nothing Then
errMsg = errMsg & vbLf & "Error on " & myCell.Row _
& "--Worksheet not found!"
ElseIf testRng Is Nothing Then
errMsg = errMsg & vbLf & "Error on " & myCell.Row _
& "--range not found!"
End If

If errMsg <> "" Then
MsgBox Mid(errMsg, 2)
Else
Set myPict = testWks.Pictures.Insert(myCell.Value)
myPict.Top = testRng.Top
myPict.Width = testRng.Width
myPict.Height = testRng.Height
myPict.Left = testRng.Left
myPict.Placement = xlMoveAndSize
End If
Next myCell
End Sub

Most of this junk just checks to make sure you typed valid stuff--the important
stuff at the bottom didn't change.

Hey Dave,
This seem to work great until I noticed that it had placed a picture on each
worksheet in the location stated. What I want is a picture to be place on a
work sheet in say the range is m1: p4.
I want a different picture on each worksheet of the workbook. I will have
25 worksheets and pictures to place. How can I stop the program from
placing a picture on each worksheet? TIA! Thanks again for the help. This
is a great start.

 

[Denny]

Thanks for the response Dave I will try this and let you know!