CODEWITHSUNDEEP
vbaexcel
Tich Mabiza
Tich Mabiza

Reputation: 13

Automatically resize image with column width

I would like to automatically resize an image so that it's width is that of a certain column range, say column A to J in Excel.

The image is a banner image that spans the width of the page on a quotation. I would like it's width to change as the column widths change so the width of the image always coincides with the width of column A to J

Upvotes: 0

Views: 1899

Answers (2)

DaveD
DaveD

Reputation: 331

Unfortunately, there is no Worksheet_CellSizeChange or any other comparable event avaiable in Excel. However, you could use the Worksheet_SelectionChange event to autofit your picture whenever you select another cell. Just paste the code in the regarding worksheet in Visual Basic Editor:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myRange As Range
    Set myRange = Me.Columns("A:J")

    Application.EnableEvents = False
    If Not Intersect(Target, myRange) Is Nothing Then
        If Not Me.Pictures(1).Width = myRange.Columns.Width Then
            Me.Pictures(1).Width = myRange.Columns.Width
        End If
    End If
    Application.EnableEvents = True
End Sub

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57683

You don't need VBA for this.

To make the control stay with a cell when the cell moves (for example, when it is sorted) and to resize the control when the cell height and width changes, select Move and size with cells.

  1. Right-click the selection, click Format Control, and then click the Properties tab.

  2. Under Object Positioning, make sure Move and size with cells is selected.

See also Position and size a control with its underlying cell on a worksheet.

Upvotes: 2

Related Questions