I am currently attaching PDF files and images to my excel sheet as OLE Objects, and trying to control the size of them. (I want the icons to appear along a grid)
The problem is that even though each OLEObject should meet the following specifications, they are sometimes different sizes. Some pdfs have greater lengths, or widths then image files.
How do I make sure they are consistent?
Public Sub OLEObjectNamesReturn()
Dim Count As Integer
Dim Space As Integer
Count = 23
Space = 0
For Each oleObj In ActiveSheet.OLEObjects
Select Case oleObj.Name
Case "CheckBox21"
Case "CheckBox22"
Case "CommandButton21"
Case "CommandButton22"
Case Else
Dim ObjectName As String
ObjectName = oleObj.Name
Set oCell = ActiveSheet.Range("P" & Count)
ActiveSheet.Shapes.Range(Array(ObjectName)).Select
ActiveSheet.Shapes(ObjectName).Height = 30
ActiveSheet.Shapes(ObjectName).Width = 30
ActiveSheet.Shapes(ObjectName).Top = oCell.Top + 7 + Space
ActiveSheet.Shapes(ObjectName).Left = oCell.Left + 7
Count = Count + 1
Space = Space + 15
End Select
Next
End Sub
See Question&Answers more detail:os