Tom’s Tutorials For Excel: Inserting a Picture at a Specific Cell on Multiple Worksheets
Suppose you have a workbook, with several worksheets that often require your company logo to be inserted at a particular cell’s location. Assuming…
• You want the picture at the top left corner of cell D3.
• The picture is named CompanyLogo.jpg
• The picture resides on your C drive in the folder named YourFilePath.
• The three destination worksheets are named Sheet1, Sheet2, and Sheet3.
…here’s one way to do that:
Sub InsertLogo()
Dim sFile As String
sFile = "C:\YourFilePath\CompanyLogo.jpg"
If Dir(sFile) = "" Then
MsgBox "Picture file was not found in path!", , "No such animal."
Exit Sub
End If
Dim pct As Picture, iLeft#, iTop#
Dim SheetList As Variant, SheetItem As Variant
SheetList = Array("Sheet1", "Sheet2", "Sheet3")
For Each SheetItem In SheetList
With Sheets(SheetItem)
With .Range("D3")
iLeft = .Left: iTop = .Top
End With
Set pct = .Pictures.Insert(sFile)
pct.Left = iLeft
pct.Top = iTop
End With
Next SheetItem
End Sub
Tom thanks for these short and pithy tutorials. I do have a okay knowledge on vba, but my constant issue is finding such objects on my own. I looked through the object browser, and failed to see .pictures being a part of worksheet class. Could you let me know how did you figure using in the way you did.
Good question, and thanks again for visiting my blog. As you’ve seen, the Object Browser does not include every single item. Some Excel features such as DATEDIF and textbox drop-downs (I can already see eyebrows raising on that one from some readers of these comments) are undocumented for reasons only Microsoft can say.
To answer your question about Pictures, it’s one of those tips I picked up over the years by haunting newsgroups. A picture is a shape and there are many different kinds of shapes that you can specify doing something with if you qualify them with the worksheet parent. An example is ActiveSheet.Pictures dot yada yada. So for a more extensive example, if you have a bunch of controls on the active sheet from the Forms toolbar, such as buttons, checkboxes, combo boxes and such, and commandbuttons from activex, you can specify a particular kind of control to manipulate such as Buttons, example:
activesheet.buttons.caption = “Click me”
which captions all Forms buttons (not those from the control toolbox) that way without looping,
or
ActiveSheet.Buttons.Delete
deletes only the Forms buttons and leaves all other Forms controls or shapes untouched.
In that regard, pictures are a kind of shape you can specify, which the code I posted does, as you saw.
I wish there was a one-stop shop for picking up all these nuances. If there is, I’ve never seen it, so I try to sprinkle my blog and Twitter entries with tips (or in this case code examples) that are not found everywhere else.
Hope this helps to answer your question. There’s always something new to learn, no matter who the expert is or how long they’ve been doing it. For me at least, it boils down to a feeling I have gotten every day over the years, that the more I learn about Excel and VBA, the more I realize how much I didn’t know as much as I thought I did.
Thanks a lot Tom, Yeah, the explanation made perfect sense. I don’t normally use the Object Model. And its from articles such as these that I learn the model. Then whilst using trying it out on a project, and applying them, that’s when you began to experiment and learn.most