CODEWITHSUNDEEP
excelvbaimageimportheader
GingerBoy
GingerBoy

Reputation: 51

Excel VBA Adding picture from HDD to my CenterHeader on my WorkSheets(1)

I have trouble making my VBA code work for the Center Header on the top of the page in Excel. I want it to load a picture (of my choosing) to the header.

It works for cell "C2", which my code currently works for, but I can't figure out how to get it to put the picture to the header instead of cell "C2".

My code is as follows (and it chooses the right picture I want to add by comparing the names and paths on the harddrive to an drop-down list. The script/code/macro is automaticly activated when I change type in the drop-down list):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myPict As Picture
Dim PictureLoc As String

If Target.Address = Range("A2").Address Then

ActiveSheet.Pictures.Delete

PictureLoc = "K:\MyPictures\" & Range("A2").Value & ".png"

With Range("C2")
    Set myPict = ActiveSheet.Pictures.Insert(PictureLoc)
    myPict.Top = .Top
    myPict.Left = .Left
    myPict.ShapeRange.LockAspectRatio = msoTrue
    myPict.ShapeRange.Width = 157
    myPict.ShapeRange.Height = 18
    myPict.Placement = xlMoveAndSize
End With

End If

End Sub

Any suggestions?

Thanks

Upvotes: 0

Views: 634

Answers (2)

L8n
L8n

Reputation: 728

To access the header of a worksheet (every worksheet is done seperate) use the PageSetup property of the worksheet object. Official MS Documentation

There you can change the CenterHeaderPicture:

Example Code from Microsoft Documentation

Sub InsertPicture() 

 With ActiveSheet.PageSetup.CentertHeaderPicture 
 .FileName = "C:\Sample.jpg" 
 .Height = 275.25 
 .Width = 463.5 
 .Brightness = 0.36 
 .ColorType = msoPictureGrayscale 
 .Contrast = 0.39 
 .CropBottom = -14.4 
 .CropLeft = -28.8 
 .CropRight = -14.4 
 .CropTop = 21.6 
 End With 

 ' Enable the image to show up in the center header. 
 ActiveSheet.PageSetup.CenterHeader = "&G" 

End Sub

Upvotes: 1

Rory
Rory

Reputation: 34045

You need to access the PageSetup object like this:

With ActiveSheet.PageSetup
    .CentertHeaderPicture.Filename = PictureLoc
    .CenterHeader = "&G"
End With

Upvotes: 2

Related Questions