OLEObjects.Add to right hand side of cell

310 views Asked by At

I want to create a button that opens an image.

This code works (if there's some text/numbers already inside column A, before you run it), but it will put the button on the left hand side of the B column.

How can I adjust the code so that the button goes to the right hand side of the A column instead?

Sub Macro1()
    Application.ScreenUpdating = False
    
        Dim cell As Range
        For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
            If Not IsEmpty(cell) Then
                Dim ol As OLEObject
                Set ol = ActiveSheet.OLEObjects.Add( _
                                                    Filename:="C:\screenshot.png", _
                                                    Link:=False, _
                                                    DisplayAsIcon:=True)
                With ol
                    .Top = cell.Offset(0, 1).Top
                    .Left = cell.Offset(0, 1).Left
                    .ShapeRange.LockAspectRatio = msoFalse
                    .Height = 13
                    .Width = 10
                End With
            End If
        Next
    Application.ScreenUpdating = True
End Sub
0

There are 0 answers