Add image at the correct location?



Want to create a default image if no picture location exist or is empty

I can't get the default image at the correct location ?

It keeps the default image everywhere instead on the correct empty cell location.

As you can see Sam Nobody has 1 picture location and other none on the sheet artdb

Hope someone can helpme what we are doing wrong

Because pictures are loading perfectly.

Added a screenshot on sheet2




Selected Answer

Start with this principle:- All basic information is contained in Sheets("ArtDB").

  • Therefore, once you have located the applicable row in that table you have already retrieved all basic information about that row.
  • Therefore you don't need to retrieve that information again.

You decided correctly to split off the code for inserting the image into a dedicated procedure. It's arguably wrong, however, not to keep this procedure in the same module because it won't be called from anywhere else. An advantage of having the procedure Private is that you don't need to specify the sheet in it. Conversely, if the sheet isn't the on the sheet in whose code module you place the procedure it would be better off in a standard code module. But that's not the case here.

Having decided on a dedicated procedure it becomes clear that you need to transfer the information you already have to that procedure. You do that by endowing your procedure with parameters to which you pass arguments in the call. Make a list what you want to pass:-

  1. Target As Range = the cell where to insert the picture
  2. PicName As String =  the name of the picture file

Based on the initial principle, anything you need but don't have should be added to the database. In your case only the path is missing. You decided correctly to keep the path to PicName in the dedicated procedure. It would need to be in the db if it were variable. Here it is not and therefore you don't need to have it in the ArtDB. Therefore, this is your function call. Observe that PicName is passed ByVal so that any change made to the variable in the sub will not change the value of the original name.

Private Sub LoadPic(Target As Range, _
                         ByVal PicName As String) 

    Debug.Print Target.Address(0, 0), PicName       ' for testing
End Sub

The following code inserts the default picture if the one specified can't be found.

    Const DefaultPic    As String = "Tmp.jpg"
    Dim PicPath         As String

    PicPath = ThisWorkbook.Path & "\"               ' must end on backslash
    If Dir(PicPath & PicName) = "" Then PicName = DefaultPic

And now, about inserting the picture.
A picture can't be inserted into a cell. Images are displayed on a separate layer of the worksheet where they can be moved to any location. You already know that from buttons and other shapes which also use this layer. Specify a picture's location by its top-left corner. Align a picture with a cell by placing its TopLeft corner exactly on the TopLeft corner of a cell. Making the size of the picture the same as the cell's (or making the cell the same size as the pictujre) is a different operation which isn't mandatory in any way.

The next job is to create an object of the picture. It is this object that you will want to move to a particular location on the worksheet's other layer. You already have the code but you made the mistake of creating a Selection object instead of an.

    Dim Pic             As Object
    Set Pic = ActiveSheet.Pictures.Insert(PicPath & PicName)

Using Worksheets("Sheet1") instead of ActiveSheet is not a mistake but if the procedure is Private I would prefer ActiveSheet. because the definition is unambiguous due to the code's location.

Now that the picture exists in the worksheet you can move it to where you want, resize it and change others of its properties as you already know how to do.

    With Target
        Pic.Top = .Top
        Pic.Left = .Left
        Pic.Width = .Offset(0, 1).Left - .Left
        Pic.Height = .Offset(1, 0).Top - .Top
    End With

The problem resolved here is that cell's width and height are measured in "characters" whereas their Top and Left are defined in points. So, the width of a cell, expressed in points, is the difference between its own Left and the adjacent cell's Left. I don't know if you want the picture sized according to the cell. The above code can be used to set the size of the image in any way you like.



Thanks Variatus.
Yes the image must fit into the cell ;)
Happy to learn again something new
GhostofWanted (rep: 24) Mar 21, '21 at 4:13 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login