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

How is it now determined that when I indicate in cell 'C4' that image apple.jpg or image banana.jpg appears in the header?

That is not what you requested in post#1 - you said the value in the cell would be a number, and the image name is Image followed by that number
if you want to use a different string amend the line beginning..

ImageName =

 

[aBcdcg]

Re: VBA change image header on cell valeu

Unfortunately, this code does not work for me and we do not understand each other.
I'm looking further, thank you for your time.

 

[Yongle]

Re: VBA change image header on cell valeu

If you give up too quickly you will never understand VBA. Simply saying that "the code not work for me" does not give me anything to work with so that I can help you - there are ways to get the code to tell you what is wrong.

The code works (I have tested it) and I know we can make it work for you. You tested the code at the beginning and you said it worked. The only thing that's been added is to create a jpg of the image. The only reason for the code not to work is if VBA cannot find an image named the same as string ImageName

I will send you some code tomorrow that will help you understand where the problem is - it will be a very simple fix

 

[aBcdcg]

Re: VBA change image header on cell valeu

If you give up too quickly you will never understand VBA.

That's 100% true.

Simply saying that "the code not work for me" does not give me anything to work with so that I can help you - there are ways to get the code to tell you what is wrong.

Thanks for your time, I appreciate.

The code works (I have tested it) and I know we can make it work for you. You tested the code at the beginning and you said it worked. The only thing that's been added is to create a jpg of the image. The only reason for the code not to work is if VBA cannot find an image named the same as string ImageName

The code in the beginning works for sure. Whit the last code I've read the debugging, and VBA will not find a image as named in the string. That's why I asked where VBA looks for the image.

I will send you some code tomorrow that will help you understand where the problem is - it will be a very simple fix

Many thanks. I'm not going to tell you that I can write a VBA code from the source, because I can not do that well enough. However, I can read it but I'm really stuck now.

Thanks for your time!

 

[Yongle]

Re: VBA change image header on cell valeu

As promised here is some help to make it easy for you to find out why the code is not working for you

1. Delete OLD code - delete anything named either InsertImageInHeader or AddJpegToHeader
- check list of macros and make sure!

2. Paste AddJpegToHeader as provided in Post#9 into module

3. Prevent temporary file being deleted by putting an apostrophe before line

Kill fPath

[COLOR=#808080][I]like this[/I][/COLOR]
[B][COLOR=#ff0000]'[/COLOR][/B]Kill fPath

4. Run the code again and check print preview to see if image is in the header (all sheets!)
- if it is still not working then move onto step 5

5. Add a new module and paste code below into that module

6. run TestThings
- a message box lists everything including the length of image names

7. Now you have information to investigate what is wrong
- do image names match? (perhaps VBA is looking for "Image1" but the name is "Image 1" or "Image1 " etc)
- does temporary file exist? It should. Look at it. Is it the correct image? Is it a blank image?
etc

Let me know how you get on
Thank you

Function ListShapes(aSheet As Worksheet) As String
    Dim shp As Shape, msg As String
    For Each shp In aSheet.Shapes
        msg = msg & vbCr & shp.Name & vbTab & Len(shp.Name)
    Next
    ListShapes = msg
End Function

Function FileExists(FPath As String) As Boolean
    Dim TestStr As String: TestStr = ""
    On Error Resume Next
    TestStr = Dir(FPath)
    On Error GoTo 0
    If TestStr = "" Then
        FileExists = False
    Else
        FileExists = True
    End If
End Function

Sub TestThigs()
Const v = vbCr & vbCr
Dim m1 As String, m2 As String, m3 As String, m4 As String

'which image is VBA looking for?
    Dim ImageName As String
    ImageName = "Image" & Sheets("Rapport").Range("C4").Value
    m1 = "Looking for:" & vbCr & ImageName & vbTab & Len(ImageName)

'list all shapes in sheet
    Dim ws As Worksheet: Set ws = Sheets("Header")
    m2 = "List of shapes:" & ListShapes(ws)
    
'which temporary file is being created
    Const fName = "TemporaryJpegForHeader.jpg"
    Dim FPath As String: FPath = ThisWorkbook.Path & "\" & fName
    m3 = "Temp file:" & vbCr & FPath
    
 'does it exist
    m4 = "Temp File exists is " & FileExists(FPath)
    
MsgBox m1 & v & m2 & v & m3 & v & m4
End Sub

 

[aBcdcg]

Re: VBA change image header on cell valeu

Very very very late reaction from me, to busy with work. If got the code working, thanks for that. What I now need to do is to look how I cn make it work whit a temp file in the sheet in stead of out of the sheet.

Many thanks !