none
Remove picture link from code RRS feed

  • Question

  • This is my code. When pictures viewed from another pc pictures can not be displayed how do i solve this?

    Sub InsertPicture()
    Dim myPicture As String, MyObj As Object
     
    myPicture = Application.GetOpenFilename _
    ("Pictures (*.gif; *.jpg; *.bmp; *.tif),*.gif; *.jpg; *.bmp; *.tif", _
    , "Select Picture to Import")
     
    If myPicture = "False" Then Exit Sub
    Range("C8:G8").Select
    Set MyObj = ActiveSheet.Pictures.Insert(myPicture)

    With MyObj
    With .ShapeRange
    .LockAspectRatio = False
        .Top = Range("C8").Top
        .Left = Range("C8").Left
        .Height = Range("C8").MergeArea.Height
        .Width = Range("C8").MergeArea.Width

    End With
    .Placement = xlMoveAndSize

    End With
     
    Set MyObj = Nothing
     
    End Sub
    Tuesday, October 18, 2011 6:07 AM

All replies

  • I can't replicate the problem. I can create a new workbook and run your code to insert a picture and save the workbook. I copied the file to an external drive and then connect it to another stand alone computer and it works fine. (The picture is not in the external drive and the workbook was copied to the hard drive of the 2nd computer before opening.)

    Googling the problem indicates that you are not the only one with the problem. What is the size of the Excel file that contains the picture? (Right click the file name and select properties to view size if it is not displayed in windows explorer.)

    How much memory in each of the computers? One thing I saw was if the user clicked where the picture should be then they see the outline and if they attempt to move it then the picture is displayed. Speculation only but I wonder if this might be because virtual memory is being used when the the workbook is opened and then only really accessed when it is required.

    Try running the code in a new workbook and see what happens.


    Regards, OssieMac
    Tuesday, October 18, 2011 7:09 AM
  • The workbook can be configured to show/hide objects or show placeholders only. What does this return

    ?ActiveWorkbook.DisplayDrawingObjects

    it should be -4104 or xlDisplayShapes

    Peter Thornton

    Tuesday, October 18, 2011 10:43 AM
    Moderator
  • Hi.

     

    I am doing this on drop box. But when opened on another pc the picture does not display??

    Tuesday, October 18, 2011 11:10 AM
  • Sorry new to this how do i do this?? I am doing this on drop box but when another pc opens it the pictures do not display??
    Tuesday, October 18, 2011 11:11 AM
  • Not sure what you mean by "drop box" but workbooks have a property which determines how images (objects) are displayed.

    2007, Excel options, Advanced, Display options for workbook
    2003, Tools, options, View, Objects

    Normally by default new workbooks will display objects, in older systems maybe not.

    What value is returned if your user runs this (ensure the workbook is active before running it)

    Msgbox ActiveWorkbook.DisplayDrawingObjects

    Peter Thornton

    Tuesday, October 18, 2011 12:49 PM
    Moderator
  • Hi Peter.

     

    I am in excel 2010. I am new at this so the above is?? Do i add this to my macro where??

     

    John

    Tuesday, October 18, 2011 3:46 PM
  • You can add the code as a simple one line procedure, simply

    [vbNet]
    Sub TestDisplayDrawingObjects()
       MsgBox ActiveWorkbook.DisplayDrawingObjects

    End Sub
    [vbnet]

    This should be called by the user when the problem workbook is active.

    If your user knows how to open the VBE editor ask him to paste the following in the Immediate window, Ctrl-g, and hit enter.

    But first have you looked the options setting in your own pc. Experiment making changes, does that replicate and fix your problem. Then you can either ask user to make the changes manually or include an additional line of code in your own macro.

    Peter Thornton

    Tuesday, October 18, 2011 4:14 PM
    Moderator
  • Hi

     

    I added this to my code but errors where to i add it?? Please remember i am new so i paste it in but wont work.

     

    Thanks

    Tuesday, October 18, 2011 5:21 PM
  • If you pasted the code as written the only reason it could fail is if there is no active workbook, which I guess is unlikely. Explain what you mean by "errors", what is the error message, where did you include the code, show us your code.

    Peter Thornton

    Tuesday, October 18, 2011 5:34 PM
    Moderator
  • This is what i done but it highlights sub insert picture in yellow??

     

    Sub InsertPicture()
    Dim myPicture As String, MyObj As Object
     
    myPicture = Application.GetOpenFilename _
    ("Pictures (*.gif; *.jpg; *.bmp; *.tif),*.gif; *.jpg; *.bmp; *.tif", _
    , "Select Picture to Import")
     
    If myPicture = "False" Then Exit Sub
    Range("C8:G8").Select
    Set MyObj = ActiveSheet.Pictures.Insert(myPicture)
     Application.CutCopyMode = False
    With MyObj
    With .ShapeRange
    .LockAspectRatio = False
        .Top = Range("C8").Top
        .Left = Range("C8").Left
        .Height = Range("C8").MergeArea.Height
        .Width = Range("C8").MergeArea.Width
    Sub TestDisplayDrawingObjects()
       MsgBox ActiveWorkbook.DisplayDrawingObjects

    End Sub
    End With
    .Placement = xlMoveAndSize

    End With
     
    Set MyObj = Nothing
     
    End Sub

    Tuesday, October 18, 2011 6:09 PM
  • You can't put a Sub() inside a Sub() as you have done, take it out!

    The Msgbox line should not be included in your main routine, it's only for diagnosing what the problem might be on a remote user's machine. What version of Excel does your user have, if 2007 it's probably not related to what I suggested, but if 2003 probably is.

    Have you tried what I suggested earlier, manually change the settings yourself in Options.

    Peter Thornton

    Tuesday, October 18, 2011 6:45 PM
    Moderator
  • If i insert the pictures manually and resizing and position it works perfect to all users. The problem started after i wrote the macro! We are all on office 2010. The problem is in the code manually it works but takes to much time.

     

    When you say a sub in a sub?? i am lost and new so i need my code edited so i can learn and understand thanks

    Tuesday, October 18, 2011 8:39 PM
  • Two separate subs, written like so. Select and run the second one...

    Sub InsertPicture()
    Dim myPicture As String, MyObj As Object
     
    myPicture = Application.GetOpenFilename _
    ("Pictures (*.gif; *.jpg; *.bmp; *.tif),*.gif; *.jpg; *.bmp; *.tif", _
    , "Select Picture to Import")
     
    If myPicture = "False" Then Exit Sub
    Range("C8:G8").Select
    Set MyObj = ActiveSheet.Pictures.Insert(myPicture)
     Application.CutCopyMode = False
    With MyObj
    With .ShapeRange
    .LockAspectRatio = False
        .Top = Range("C8").Top
        .Left = Range("C8").Left
        .Height = Range("C8").MergeArea.Height
        .Width = Range("C8").MergeArea.Width
    End With
    .Placement = xlMoveAndSize

    End With
     
    Set MyObj = Nothing
     
    End Sub

    Sub TestDisplayDrawingObjects()
       MsgBox ActiveWorkbook.DisplayDrawingObjects

    End Sub


    HTH, Bernie
    Wednesday, October 19, 2011 3:27 PM
  • Another thing you might try, after inserting the picture try selecting it (normally should never need to use Select)

    MyObj.Select
    If that works afterwards select a cell, otherwise try this as the last line of your code

    Application.ScreenUpdating = True

    Normally this shouldn't be necessary either but worth a try

    Peter Thornton

    Wednesday, October 19, 2011 8:51 PM
    Moderator
  • Does not solve my problem. Thing is when someone does this in excel 2007 and i open in 2010 it works fine.

    But if done in 2010 the picture will not display "the link to this picture......"??

    Saturday, October 29, 2011 4:46 PM
  • Does not solve my problem. Thing is when someone does this in excel 2007 and i open in 2010 it works fine.

    But if done in 2010 the picture will not display "the link to this picture......"??
    Saturday, October 29, 2011 4:46 PM