none
Copying Picture to Active Worksheet from Another Worksheet Problem RRS feed

  • Question

  • I am having a strange issue here.  I want to copy a picture from one worksheet to another.  The copy seems to work, but the image seems to be empty in the destination worksheet.  Basically, when I identify where I want to have the picture, if it is there in the sheet, I move it to the desired position. If not, I try to copy it from another sheet.  If it's not in any sheets, I have code to browse to the image file and insert it in the worksheet.  That last part works, but the copy part doesn't.

    Here is a simplified sample of my "find and copy" code:

            'Copy or build picture only if we are on a designated date cell.
            If IsDateCell(Target) Then
                
                'If it's in another worksheet, copy and paste it here.
                For Each ws In ActiveWorkbook.Sheets
                    Set calBtn = get_calendar(ws)
                    If Not calBtn Is Nothing Then
                        calBtn.Copy
                        noOfShapes = ActiveSheet.Shapes.Count
                        ActiveSheet.Pictures.Paste.Select
                        ActiveSheet.Shapes(noOfShapes + 1).Name = calButton
                        Set calBtn = Nothing
                        Set calBtn = get_calendar(ActiveSheet)
                        calBtn.OnAction = "modCalendar.picCalendar_Click"
                        isOK = True
                        Exit For
                    End If
                Next
                
    

    So IsDateCell just tells me if I'm on the right cell (true/false).
    calBtn is declared as a shape object, and get_calendar function returns the picture object if it's in the the supplied worksheet.

    Basically, if the code finds the picture in another worksheet, it copies it, then pastes it into the active worksheet.  I'm using this picture as a button, hence setting the .OnAction property.

    So the problem is, the pasted picture comes in "empty".  I can see the frame of it if I right-click on it, I can click on it and it will run the proper code, it just looks "empty".  Unfortunately this does not always happen, it's arbitrary.  Sometimes the picture shows up just fine.  Obviously I can't give this code to my users if it's not working consistently.

    Any ideas why this might happen?  Any suggestions how to fix?  Thanks in advance...


    Ron Mittelman

    Thursday, December 12, 2013 12:31 AM

Answers

  • Re:  copy and paste a picture

    1.  I am not sure what is going on, so to be safe, I should point out that:
         When looping thru the sheets in a workbook...
             For Each ws In ActiveWorkbook.Sheets
             Next
        "ws" does not become the active sheet, it must be selected... ws.Select

    2.  If you want to copy a picture of calBtn use... calBtn.CopyPicture
         (assumes calBtn is a Shape object)

    3.  To paste the copied picture, you must first select the cell where it is to go.
         To select the cell, the sheet it is on must be the active sheet.
         The paste code then is:  ActiveSheet.PasteSpecial

    4.  If you don't want to select the destination cell, the following may work...
         ActiveSheet.Paste Destination:=ActiveSheet.Cells(row#, column#)
         -or-
         ws.Paste Destination:=ws.Cells(row#, column#)

    '---
    Jim Cone
    Portland, Oregon USA
    free & commercial excel programs
    https://goo.gl/IUQUN2 (Dropbox)


    • Marked as answer by RMittelman Thursday, December 12, 2013 5:00 PM
    • Edited by James Cone Tuesday, October 18, 2016 5:06 PM
    Thursday, December 12, 2013 4:08 AM

All replies

  • Re:  copy and paste a picture

    1.  I am not sure what is going on, so to be safe, I should point out that:
         When looping thru the sheets in a workbook...
             For Each ws In ActiveWorkbook.Sheets
             Next
        "ws" does not become the active sheet, it must be selected... ws.Select

    2.  If you want to copy a picture of calBtn use... calBtn.CopyPicture
         (assumes calBtn is a Shape object)

    3.  To paste the copied picture, you must first select the cell where it is to go.
         To select the cell, the sheet it is on must be the active sheet.
         The paste code then is:  ActiveSheet.PasteSpecial

    4.  If you don't want to select the destination cell, the following may work...
         ActiveSheet.Paste Destination:=ActiveSheet.Cells(row#, column#)
         -or-
         ws.Paste Destination:=ws.Cells(row#, column#)

    '---
    Jim Cone
    Portland, Oregon USA
    free & commercial excel programs
    https://goo.gl/IUQUN2 (Dropbox)


    • Marked as answer by RMittelman Thursday, December 12, 2013 5:00 PM
    • Edited by James Cone Tuesday, October 18, 2016 5:06 PM
    Thursday, December 12, 2013 4:08 AM
  • Hi Jim,

    Thanks for your reply.

    Point1: I know ws is not the active sheet, but it IS a sheet, and that's all I want, in order to find and copy the image from it.

    Point2: I will try this and if it works, you get the answer credit.

    Point3: I'm not sure whether this is true.  ActiveSheet.Pictures.Paste DOES seem to put the picture on the sheet, but I'm not sure where on the sheet.  Granted if you want it at cell A1, you would need to select A1.

    I will try point 4 out.  I'm betting my hopes on point 2.

    Thanks again...


    Ron Mittelman

    Update: CopyPicture doesn't help the issue, the picture still is there at desired point, but it has no image, it's invisible.  Still, the rest of the code works, because when I hover the mouse arrow over where it's supposed to be, the cursor turns into the pointing finger.

    Update 2: Got it working! After a lot of trial and error with your suggestions, I found that selecting a cell just won't work. Using ActiveSheet.Paste DOES work, without any parameters. But only if you did "calBtn.Copy", not "calBtn.CopyPicture".  Go figure.  In any case, it now pastes the picture/shape object WITH its image consistently.  Thanks for the various suggestions, they are what led me to the final solution.

    • Edited by RMittelman Thursday, December 12, 2013 5:00 PM status update
    Thursday, December 12, 2013 4:15 PM
  • Final comment:

    This thread was started to solve an issue I encountered while developing a popup calendar control to use in my Excel workbooks without having to rely on an ActiveX control.  This was the final step in accomplishing that goal.

    The popup calendar consists of a simple UserForm and module, and behaves similarly to a DatePicker or MonthView control.

    Anybody interested in using this control, please feel free to reply to this thread with your email address, and I will be happy to forward the control to you.

    Thanks again for the help I received on this issue.


    Ron Mittelman



    • Edited by RMittelman Friday, December 13, 2013 9:58 PM
    Thursday, December 12, 2013 5:33 PM
  • Ron,
    Re:  Date Picker

    I've got one too...
    The free DatePicker add-in for Excel can be downloaded from...
    https://goo.gl/IUQUN2 (Dropbox)
    (no ads, no trackers, no cookies, no registration)

    After installation, right-click any cell and choose DatePicker from the pop-up menu.
    Does not work on xl2013+
    ----
    Jim Cone
    Portland, Oregon USA

    Image




    • Edited by James Cone Tuesday, October 18, 2016 5:07 PM update link
    Thursday, December 12, 2013 6:29 PM
  • Hi,

    I know it's a long shot but would you still have the above datepicker code you could forward to me?

    grahamdaleis@yahoo.co.uk

    Rgds

    Graham

    Friday, October 16, 2015 7:24 AM
  • I will search for it.  I don't exactly remember the project I was working on.

    Ron Mittelman

    Tuesday, October 27, 2015 3:30 PM
  • Sorry Graham, I can't seem to find it.  I did it 2 years ago and on an old job I had.

    Ron Mittelman

    Thursday, October 29, 2015 10:56 PM