Excel 2003 Macro, no longer working in 2010

[MRichmond]

I have a macro that works perfectly in Excel 2003, however now I have been "upgraded" to Excel 2010, and my macro no longer works as it should.

Would anyone be able to look at the code, and suggest changes to make it work in both versions please (some users are still on Excel 2003)

Sub insertpicture()
Dim sPicture As String, pic As Picture
 
'Opens dialogue box to be able to search for picture to insert
 
sPicture = Application.GetOpenFilename _
    ("Pictures (*.gif; *.jpg; *.bmp; *.tif), *.gif; *.jpg; *.bmp; *.tif", _
     , "Select Picture to Import")
 
If sPicture = "False" Then Exit Sub
'Resizes the picture to fit within the allotted area on the spreadsheet
 
Set pic = ActiveSheet.Pictures.Insert(sPicture)
With pic
    .ShapeRange.LockAspectRatio = msoFalse
    .Height = 135
    .Width = 255
    .Top = ActiveCell.Top
    .Left = ActiveCell.Left
    .Placement = xlMoveAndSize
End With
 
'Compresses the picture
 
Set pic = Nothing
    Dim octl As CommandBarControl
    
    Set octl = Application.CommandBars.FindControl(ID:=6382)
    Application.SendKeys "%A%W~"
    octl.Execute
End Sub

The code is designed to to open a dialogue box to enable the user to select the picture they wish to add to the spreadsheet. Once selected it resizes the picture to fit a cell range. Finally it compresses the picture (our guys have a habit of trying to put 3mb pictures on this form).

When I say it doesnt work any longer, what it appears to be doing is actually inserting a link to the picture, which looks ok on the users PC, but when it arrives in someone else's inbox, the pictures have been replaced by links.

[Bob Phillips]

What is control ID 6382? Does it still exist with the Ribbon?

[mancubus]

hi. have seen stg like this:

Application.SendKeys "%(oe)~{TAB}~"

Application.CommandBars.ExecuteMso "PicturesCompress"

[MRichmond]

Thanks Mancubus. I've replaced the two lines with your suggestions however now the macro wont run at all, it highlights the Name?? Any suggestions?

XLD Control ID 6382 is the Compress Picture dialogue box, and whilst I can find it on the ribbon I'm assuming it's been replaced by the line Mancubus has supplied.

[mancubus]

http://answers.microsoft.com/en-us/o...a-6bd908469f1a