Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2006
    Posts
    162

    Unanswered: Image into single cell

    How can i get an image to resize and fit in a single cell? Ideal would be able to drag and drop pics, but this won't work unless there is a work around using vba.

    Another option is to have a button/link to add image.

    TIA

  2. #2
    Join Date
    Sep 2006
    Posts
    162
    Any code that will ask for location of image and resize and insert??

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    Perhaps another work around is to put the image into a Comment for that cell. Will that work?

    A picture can be included in a Comment to store a photo of a product or employee near the cells that contain that specific information.

    1. Select cell that contains a Comment, right-click and from the shortcut menu select Show Comment.
    2. Select the edge of Comment so that Comment is surrounded by dots, not by slashes.
    3. Right-click and select Format Comment > Colors and Lines > Fill.
    4. Open the Color box > Fill Effects > click the Picture tab, and click select Picture.
    5. Select a picture and click OK. Resize Comment as appropriate.
    Last edited by shades; 02-28-07 at 14:37.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Sep 2006
    Posts
    162
    I was looking into that as an option. What would I have to do to make a vba code to do this auto. I want a link that will ask location of file then add to comment for that cell.

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Tom Urtis had developed something that you might adapt.

    Code:
    Sub PlaceGraph()
    ' Page 306
    Dim x As String, z As Range
    
    Application.ScreenUpdating = False
    x = "C:\XWMJGraph.gif"
    
    Set z = Worksheets("ChartInComment").Range("A3")
    
    On Error Resume Next
    z.Comment.Delete
    On Error GoTo 0
    
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Export x
    
    With z.AddComment
        With .Shape
            .Height = 322
            .Width = 465
            .Fill.UserPicture x
        End With
    End With
    
    Range("A1").Activate
    Application.ScreenUpdating = True
    
    Set z = Nothing
    End Sub
    This puts a graph into the comment for cell A3. Modify the path, and cell as needed. Depending on your set up, you could have all images in one directory and labeled in such a way that you could set up a loop in this code and assign each image in one step.

    the reason this is a preferred action to actually in the cell itself, is that you don't have to worry about screen resolution, and pixel differences and positioning of the cell relative to pixel placement of image.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Sep 2006
    Posts
    162
    The pic is gonna change on a often, its for a qc tech who will take photos of work that need to be added to the spreadsheet as he goes. Any code available to pull a prompt up to browse for the image.

    Found this

    Sub Macro1()

    Dim fn
    fn = Application.GetOpenFilename 'can add parameters. See help for details.
    If fn = False Then
    MsgBox "Nothing Chosen"
    Else
    MsgBox "You chose " & fn
    'now that you have the name, you can open it or do something else
    End If

    End Sub
    How can i set the file i select as a variable and make it a fill for a comment.
    Last edited by Jay59; 02-28-07 at 17:19.

  7. #7
    Join Date
    Sep 2006
    Posts
    162
    Found this, gonna work on using it with the last snippet.

    Sub AddPictureToComment()
    Dim rng As Range
    Dim shp As Comment

    Set rng = ActiveCell

    If Not rng.Comment Is Nothing Then
    rng.Comment.Delete
    End If

    If rng.Text <> "" Then
    Set shp = rng.AddComment("")
    shp.Shape.Fill.UserPicture rng.Text
    End If

    End Sub

  8. #8
    Join Date
    Oct 2003
    Posts
    1,091
    On the code I posted you can omit these lines:

    Code:
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Export x
    Since that was related to a chart, not an image.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  9. #9
    Join Date
    Oct 2003
    Posts
    1,091
    To look for a file, try this:

    Code:
        myFile = Application.GetOpenFilename
        Workbooks.Open Filename:=myFile
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  10. #10
    Join Date
    Sep 2006
    Posts
    162
    This is what I came up with.

    Sub Macro()

    Dim rng As Range
    Dim shp As Comment


    Set rng = ActiveCell

    If ActiveCell.Value = "Click to add pic" Then

    Dim fn
    fn = Application.GetOpenFilename
    If fn = False Then
    Else
    Set shp = rng.AddComment("")
    shp.Shape.Fill.UserPicture fn
    shp.Shape.Height = 322
    shp.Shape.Width = 465
    End If
    ActiveCell = "QC Pic"

    Else
    End If

    End Sub
    Last edited by Jay59; 03-01-07 at 18:52.

  11. #11
    Join Date
    Oct 2003
    Posts
    1,091
    So, I assume you got it to work the way you want?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  12. #12
    Join Date
    Sep 2006
    Posts
    162
    Yeap, just gotta get it to run the macro when you select the cell.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •