Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    selecting pictures (excel97)

    Hello everyone

    I would like to be able to copy a picture
    The picture that I want to copy doesn

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: selecting pictures (excel97)

    I am not clear what you are after completely. If you don't know the name of the shape, are you just looking for the only shape on the sheet? If there is only shape on the sheet, the name can be obtained:

    sName = ActiveSheet.Shapes(1).name

    Though you could just copy it with:
    ActiveSheet.Shapes(1).Copy

    If there are more than 1 if you are trying to find a name, I don't understand what your code is supposed to do...

    Concerning finding a piture in a particular cell. This will copy to the clipboard the shape that is in Cell G3. It loops thru the shapes collection and tests the range occupied by the shape (using the TopLeftCell and BottomRightCell properties)

    <pre>Option Explicit
    Sub SelectShape()
    Dim shp As Shape
    Dim rng As Range
    Dim sCell As String
    sCell = "G3"
    For Each shp In ActiveSheet.Shapes
    With shp
    Set rng = Range(Range(.TopLeftCell.Address), _
    Range(.BottomRightCell.Address))
    If Not Intersect(Range(sCell), rng) Is Nothing Then
    shp.Copy
    MsgBox "'" & shp.Name & "' in Cell " & _
    sCell & " has been copied"
    GoTo ExitMe
    End If
    End With
    Next
    MsgBox "There are no shapes in " & sCell
    ExitMe:
    Set rng = Nothing
    Set shp = Nothing
    End Sub</pre>


    Perhaps it can be adapted to meet your needs...

    Steve

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: selecting pictures (excel97)

    Thank you Steve

    One of things that I wanted to do was to be able to copy a picture from a certain cell which the second part of your reply answered for me.

    The other thing that I wanted to do was to be able to find a picture that could be anywhere on the sheet.
    There are a number of shapes on the sheet (buttons and pictures)
    The code I posted before was looking for any picture on the sheet, and I was trying to find it by looking for "picture 1" then "picture 2" then "picture 3"

    I since realised that I need the picture that was last added to the sheet
    So I adapted what I learn from your reply and came up with a solution
    I counted how many shapes are on the sheet
    then I worked backwards from the last shape testing to see if it was a picture
    then I copied the picture

    Sub CopyPicture()
    Dim i As Integer
    Dim shp As Shape
    'count the number of shapes
    i = 0
    For Each shp In ActiveSheet.Shapes
    i = i + 1
    Next

    'Find the last shape that is a picture
    Dim sName As String
    Do
    sName = ActiveSheet.Shapes(i).Name
    If Left(sName, 7) = "Picture" Then
    ActiveSheet.Shapes(i).Copy
    Range("A1").Select
    ActiveSheet.Paste
    Exit Do
    End If
    i = i - 1
    Loop
    MsgBox sName & " copied"
    End Sub


    Thank you very much for your help

Posting Permissions

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