VBA change image header on cell value

[aBcdcg]

Hello,

Is it possible, (how) to define the image on a cell value?

- If on sheet 'worksheet 1' cel A1 the value = 1 -> put image1.jpg in the rightheader.
- If on sheet 'worksheet 1' cel A1 the value = 2 -> put image2.jpg in the rightheader.
Thanks a lot !

 

[Yongle]

Re: VBA change image header on cell valeu

- the value found in cell A1 (in sheet named "Worksheet Name") is concatenated with string "Image"
- image of that name from folder fPath (C:\Folder\Subfolder) is inserted in right header

- the value in A1 can be alphanumeric eg Happy7 in A1 inserts jpeg named ImageHappy7

Sub InsertImageInHeader()
    Dim fPath As String: fPath = "[COLOR=#ff0000]C:\Folder\Subfolder[/COLOR]"
    Dim fName As String: fName = "Image" & Sheets("[COLOR=#ff0000]worksheet name[/COLOR]").Range("A1").Value
    With ActiveSheet.PageSetup
        .RightHeaderPicture.Filename = fPath & "\" & fName & ".jpg"
        .RightHeader = "&G"
    End With
End Sub

- the value in A1 can be alpha, numeric or alphanumeric eg Happy7 in A1 inserts jpeg named ImageHappy7 in the header

 

[aBcdcg]

Re: VBA change image header on cell valeu

Great, I've got this working. thanks!

Is it also possible tp place the image in the worksheet in stead of on a HDD?
I've got few people to work whit this sheet (>10), when I can place the image's in the sheet, I expect fewer problems.

Thanks !

- the value found in cell A1 (in sheet named "Worksheet Name") is concatenated with string "Image"
- image of that name from folder fPath (C:\Folder\Subfolder) is inserted in right header

- the value in A1 can be alphanumeric eg Happy7 in A1 inserts jpeg named ImageHappy7

Sub InsertImageInHeader()
    Dim fPath As String: fPath = "[COLOR=#ff0000]C:\Folder\Subfolder[/COLOR]"
    Dim fName As String: fName = "Image" & Sheets("[COLOR=#ff0000]worksheet name[/COLOR]").Range("A1").Value
    With ActiveSheet.PageSetup
        .RightHeaderPicture.Filename = fPath & "\" & fName & ".jpg"
        .RightHeader = "&G"
    End With
End Sub

- the value in A1 can be alpha, numeric or alphanumeric eg Happy7 in A1 inserts jpeg named ImageHappy7 in the header

 

[Yongle]

Re: VBA change image header on cell valeu

That is more complex but can be done
What is the name of sheet containing A1?
What is the name of sheet containing images?

Will update thread tomorrow

 

[aBcdcg]

Re: VBA change image header on cell valeu

Name of sheet containing A1 -> Rapport (A1 will be C4)

Name of sheet containing images: -> Header


What it must do:

- If on sheet 'worksheet 1' cel C4 the value = 1 -> put image1.jpg in the rightheader of all sheets, except on the first sheet ' Voorblad '. It is now set under page settings.

- If on sheet 'worksheet 1' cel C4 the value = 2 -> put image2.jpg in the rightheader.of all sheets, except sheet ' Voorblad '. it is now set under page settings.
+ place the tekst ' Pagina &[Pagina] van &[Pagina's] ' instead of ' Pagina &[Pagina]-1 van &[Pagina's]-1 ' in the LeftFooter
+ deleted the first sheet ' Voorblad ' (not needed when the value in C4 = 2)

It would be great if the value in C4 can be alpha, numeric or alphanumeric.

Thanks very much !

 

[Yongle]

Re: VBA change image header on cell valeu

Is it also possible tp place the image in the worksheet in stead of on a HDD?

Temporary jpeg is created , added to the header and then deleted

Sub AddJpegToHeader()
    Const fName = "TemporaryJpegForHeader.jpg"
    Dim fPath As String: fPath = ThisWorkbook.Path & "\" & fName
    Dim ws As Worksheet, Chrt As ChartObject, ImageName As String, shp As Shape
'create jpeg
    Set ws = Sheets("Header")
    ImageName = "Image" & Sheets("Rapport").Range("C4").Value
    Set shp = ws.Shapes(ImageName)
    shp.CopyPicture xlScreen, xlPicture
    Set Chrt = ws.ChartObjects.Add(0, 0, shp.Width, shp.Height)
    Chrt.Activate
    With Chrt.Chart
        .Paste
        .Export fPath
    End With
    Chrt.Delete
'add to header
    Sheets("[COLOR=#ff0000]SheetToPrint[/COLOR]").Activate
    With ActiveSheet.PageSetup
        .RightHeaderPicture.Filename = fPath
        .RightHeader = "&G"
    End With
'delete temp file
    On Error Resume Next
    Kill fPath
End Sub

What it must do:

- If on sheet 'worksheet 1' cel C4 the value = 1 -> put image1.jpg in the rightheader of all sheets, except on the first sheet ' Voorblad '. It is now set under page settings.

- If on sheet 'worksheet 1' cel C4 the value = 2 -> put image2.jpg in the rightheader.of all sheets, except sheet ' Voorblad '. it is now set under page settings.
+ place the tekst ' Pagina &[Pagina] van &[Pagina's] ' instead of ' Pagina &[Pagina]-1 van &[Pagina's]-1 ' in the LeftFooter
+ deleted the first sheet ' Voorblad ' (not needed when the value in C4 = 2)

Perhaps someone else will help you with this request

 

[aBcdcg]

Re: VBA change image header on cell valeu

Temporary jpeg is created , added to the header and then deleted

Sub AddJpegToHeader()
    Const fName = "TemporaryJpegForHeader.jpg"
    Dim fPath As String: fPath = ThisWorkbook.Path & "\" & fName
    Dim ws As Worksheet, Chrt As ChartObject, ImageName As String, shp As Shape
'create jpeg
    Set ws = Sheets("Header")
    ImageName = "Image" & Sheets("Rapport").Range("C4").Value
    Set shp = ws.Shapes(ImageName)
    shp.CopyPicture xlScreen, xlPicture
    Set Chrt = ws.ChartObjects.Add(0, 0, shp.Width, shp.Height)
    Chrt.Activate
    With Chrt.Chart
        .Paste
        .Export fPath
    End With
    Chrt.Delete
'add to header
    Sheets("[COLOR=#ff0000]SheetToPrint[/COLOR]").Activate
    With ActiveSheet.PageSetup
        .RightHeaderPicture.Filename = fPath
        .RightHeader = "&G"
    End With
'delete temp file
    On Error Resume Next
    Kill fPath
End Sub

At this time I don't see how this code must works.
Where does it see the images?
How does it place the image at the header of all sheets? Do I need to define "sheetToPrint"? Thats not possible because that every time different.

Perhaps someone else will help you with this request

No problem. In stead of delete the first shet I can also hide it.
Is it possible to place text from a cell in the footer?


Thanks !

 

[Yongle]

Re: VBA change image header on cell valeu

At this time I don't see how this code must works

- replace "SheetToPrint" with name of any sheet and test
- after testing use a loop to add the image to other sheets

 

[Yongle]

Re: VBA change image header on cell valeu

modified to add the jpg to the header in EVERY sheet

Sub AddJpegToHeader()
    Const fName = "TemporaryJpegForHeader.jpg"
    Dim fPath As String: fPath = ThisWorkbook.Path & "\" & fName
    Dim ws As Worksheet, Chrt As ChartObject, ImageName As String, shp As Shape
'create jpeg
    Set ws = Sheets("Header")
    ImageName = "Image" & Sheets("Rapport").Range("C4").Value
    Set shp = ws.Shapes(ImageName)
    shp.CopyPicture xlScreen, xlPicture
    Set Chrt = ws.ChartObjects.Add(0, 0, shp.Width, shp.Height)
    Chrt.Activate
    With Chrt.Chart
        .Paste
        .Export fPath
    End With
    Chrt.Delete
'add to header
   [COLOR=#ff0000] For Each ws In ThisWorkbook.Worksheets[/COLOR]
        With [COLOR=#ff0000]ws[/COLOR].PageSetup
            .RightHeaderPicture.Filename = fPath
            .RightHeader = "&G"
        End With
   [COLOR=#ff0000] Next ws[/COLOR]
'delete temp file
    On Error Resume Next
    Kill fPath
End Sub

 

[aBcdcg]

Re: VBA change image header on cell valeu

Hi,

When a image is posted in every header, that great! because for each document we created the sheets are different.

Unfortunately, I do not yet see how this macro will work.
Where does the macro see the image?
How is it now determined that when I indicate in cell 'C4' that image apple.jpg or image banana.jpg appears in the header?



Thanks you for your time!