Resize the ole object (picture) in excel sheet

[larrykathy]

Using VB, I select a photo and drag-drop it over a button. That opens another form that has an ole on it. The excel sheet opens on that form through the ole and displays nicely. Then the photo is pasted into the ole-excel sheet perfectly. All I'm trying to do now is resize the photo through code after pasting it.
'''from photo select form
Clipboard.Clear
Clipboard.SetData Image1.Picture
'''now the other form
frmCreateConcern.Show
frmCreateConcern.OLE1.DoVerb
frmCreateConcern.OLE1.object.Application.WindowState = xlMaximized
frmCreateConcern.OLE1.object.ActiveSheet.Paste
I'd like to use something like this....
Selection.ShapeRange.LockAspectRatio = msoTrue
Selection.ShapeRange.Height = 144#
Selection.ShapeRange.Width = 192#

but it says that object is not selected or set. Now on the Excel sheet it is selected. I guess my question is.. How do I refer to that object?
ole1.object.selection.ShapeRange.LockAspectRatio = msoTrue............for example

Thanks
Larry

 

[SkipVought]

Hi,

Since it's the LAST shape on the sheet then

With Activesheet.Shapes(Activesheet.Shapes.Count)
  .LockAspectRatio = msoTrue
  .Height = 144#
End With

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[larrykathy]

Thanks SkipVought but......

I need to be able to reference that photo/object I just pasted into the Excel sheet. I just keep getting "it says that object is not selected or set" error. After I discover how to reference that object then I can use your code, correct? Thanks for the reply.
Larry

 

[SkipVought]

If you can identify the Sheet Object, this code will work. You do NOT have to SELECT the object!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[larrykathy]

Here's how I finally succeeded

frmCreateConcern.OLE1.DoVerb
frmCreateConcern.OLE1.object.Application.WindowState = xlMaximized
frmCreateConcern.OLE1.object.ActiveSheet.Paste
frmCreateConcern.OLE1.object.ActiveSheet.Name = "New Concern"
frmCreateConcern.OLE1.object.ActiveSheet.Shapes("Picture 85").Select
frmCreateConcern.OLE1.object.ActiveSheet.Shapes("Picture 85").LockAspectRatio = msoTrue
frmCreateConcern.OLE1.object.ActiveSheet.Shapes("Picture 85").Width = 290#

Thanks
Larry