VBA image placement methods

Question

I'm passing an image from a vba userform into excel(2013)

I have found a method to do this

TempFile = Replace(CreateObject("Scripting.FileSystemObject").GetTempName, "tmp", "bmp")

SavePicture Image1.Picture, TempFile

With Sheets("MASTER").Pictures.Insert(TempFile)

.Left = Cells(LastLine, 1).Left

.Top = Cells(LastLine, 1).Top

.Width = Cells(LastLine, 1).Width

.Height = Cells(LastLine, 1).Height

.Placement = xlMoveAndSize

End With

This code places the image properly, however the next sequence of code - which sorts the worksheet with the new entry - fails to sort the image with its' associated row.

The image remains stationary

From my experimentation, the difference seems to be "paste" vs "insert" They both act differently.

Is anyone aware of another method or procedure that will lock the image with its row?

Answer 1

I have no interest of a dispute, you can believe me or not.

Select only cell B1, sort the cells again and you can see the difference by yourself.

Answer 2

Is anyone aware of another method or procedure that will lock the image with its row?

I am not aware of any method of reliably locking the image to the row so that it will sort with the row. However, I have used the following method whereby I use an extra temporary column to insert the row numbers before sorting and then find that row number and reposition the picture accordingly.

The extra column must be included in the sort.

In the example code I have not included code to perform the sort but you should be able to insert the sort code. Also included a UDF to find the last row number and last column number.

Sub Test()

    Dim wsMast As Worksheet

    Dim TempFile As String

    Dim LastLine As Long

    Dim picMyImg As Picture

    Dim lngLastRow As Long

    Dim lngLastCol As Long

    Dim lngNewRow As Long

    LastLine = 10       'Value assigned for testing

    'Next line used to test. Replace with your code to get the file name

    TempFile = "C:\Users\User\Pictures\Transparencies 1\IMG_0001.bmp"

    Set wsMast = Sheets("MASTER")

    Set picMyImg = wsMast.Pictures.Insert(TempFile)

    With picMyImg

        'Name the image so it can be referenced later in other subs (if required)

        .Name = "MyImage1"

        .Left = wsMast.Cells(LastLine, 1).Left

        .Top = wsMast.Cells(LastLine, 1).Top

        .Width = wsMast.Cells(LastLine, 1).Width

        .Height = wsMast.Cells(LastLine, 1).Height

        .Placement = xlMoveAndSize

    End With

    lngLastRow = LastRowOrCol(wsMast, True)

    lngLastCol = LastRowOrCol(wsMast, False)

    With wsMast

        'Insert a column header on the extra temporary column

        .Cells(1, lngLastCol + 1) = "Row Index"

        'Insert formulas in the extra column to insert the row numbers in the cells

        'The formulas will insert row numbers that will match the actual row numbers

        .Range(.Cells(2, lngLastCol + 1), .Cells(lngLastRow, lngLastCol + 1)).Formula = "=Row()"

        'Copy the values of the formulas and replace with values

        '(Code does same as  Copy -> Paste Special -> Values)

        'Note: Next 2 lines are actually one line of code with a space and underscore line break

        .Range(.Cells(2, lngLastCol + 1), .Cells(lngLastRow, lngLastCol + 1)).Value = _

        .Range(.Cells(2, lngLastCol + 1), .Cells(lngLastRow, lngLastCol + 1)).Value

    End With

    '***************************************************************

    'Insert your code here to Sort the rows as required.

    'Ensure that last column with row numbers is included

    '***************************************************************

    'Find the value in the column that will equal the value of LastRow

    'This will be the row to where the picture is to be repositioned.

    With wsMast.Columns(lngLastCol + 1)

        lngNewRow = .Find(What:=LastLine, _

                LookIn:=xlFormulas, _

                LookAt:=xlWhole, _

                SearchOrder:=xlByRows, _

                SearchDirection:=xlNext, _

                MatchCase:=False).Row

    End With

    'Re-position the Top of picture to the new row position

    With picMyImg

        .Top = wsMast.Rows(lngNewRow).Top

    End With

    'Remove the added Row Index column

    wsMast.Columns(lngLastCol + 1).ClearContents

End Sub

Function LastRowOrCol(ws As Worksheet, bolRowCol As Boolean, Optional rng As Range) As Long

    'Finds the last used Row or Column in a worksheet

    'First parameter is Worksheet

    'Second parameter is True for Last Row or False for last Column

    'Third parameter is optional. Use to find the last row or column in a specific range

    Dim lngRowCol As Long

    Dim rngToFind As Range

    If rng Is Nothing Then

        Set rng = ws.Cells

    End If

    If bolRowCol Then

        lngRowCol = xlByRows

    Else

        lngRowCol = xlByColumns

    End If

    With ws

        Set rngToFind = rng.Find(What:="*", _

                LookIn:=xlFormulas, _

                LookAt:=xlPart, _

                SearchOrder:=lngRowCol, _

                SearchDirection:=xlPrevious, _

                MatchCase:=False)

    End With

    If Not rngToFind Is Nothing Then

        If bolRowCol Then

            LastRowOrCol = rngToFind.Row

        Else

            LastRowOrCol = rngToFind.Column

        End If

    End If

End Function

Answer 3

Insert a row at the top, write "Picture" into A1, "Nr" into B1, then you can sort column B and the pictures moves with the sorted cells.

Andreas.

Answer 4

Thanks Andreas

Not quite sure about that speculation though

This is the simplest of scenarios. 2 columns, 2 entries, 2 images

No headers, no labeling.

works just fine.

perhaps I'm misunderstanding?

Answer 5

The most reason is that Excel can not recognize that the cells are interrelated.

E.g. the heading of column A is "Nr" and the cells below have numbers and column B contain the pictures. In this case the pictures are not sorted when you sort column A.

When you write just a header in column B, Excel recognize that column B is related to column A and sorts the pictures in column B if you sort column A.

So the issue is not the Placement property, it is the layout of your file.

Andreas.