How to format an OLEObject in VBA?

Question

I have a code for inserting an attached file to a certain column and resizing it so that it perfectly fills the cell. Only problem I have now is that the object is just a blank rectangle and hard to spot if there is even anything in the cell.

I've tried IconLabel:=Range("A" & ActiveCell.Row) so that it shows the ID # of the row but it seems to show it very stretched out and to the point where you can't see anything.

Sub Macro1()
    Range("X" & ActiveCell.Row).Select

    Dim vFile As Variant, Sh As Object
    vFile = Application.GetOpenFilename("All Files,*.*", Title:="Find file to insert")
    If vFile = False Then Exit Sub

    Dim OleObj As OLEObject
    Set OleObj = ActiveSheet.OLEObjects.Add(Filename:=vFile, Link:=False, DisplayAsIcon:=True, IconFileName:= _
        "C:\WINDOWS\Installer\{90110409-6000-11D3-8CFE-0150048383C9}\xlicons.exe", _
        IconIndex:=0, IconLabel:=Range("A" & ActiveCell.Row).Value)
    OleObj.ShapeRange.LockAspectRatio = msoFalse
    OleObj.Height = Range("X" & ActiveCell.Row).Height
    OleObj.Width = Range("X" & ActiveCell.Row).Width
End Sub

it works but the number is tiny. I want it to be expanded so that it is visible, but without having to extend the whole column/row to a big size... — aNobleNoob, Jun 17 '19 at 17:52

score -1 · Answer 1 · answered Jun 17 '19 at 17:10

-1

This would make the cell red, because of the vbRed, furthermore, it would be about 4 times less than the standard cell:

With OleObj
    .ShapeRange.LockAspectRatio = msoFalse
    .Height = Range("X" & ActiveCell.Row).Height / 2
    .Width = Range("X" & ActiveCell.Row).Width / 2
    .Interior.Color = vbRed
End With

Thus, it would be different and visible. These are the other built-in colors, from the VBA library (Press F2):

answered Jun 17 '19 at 17:10

Vityata

41,328
7
50
86

[Try to avoid Select and Activate in VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Vityata Jun 17 '19 at 17:13

How to format an OLEObject in VBA?

1 Answers1