CODEWITHSUNDEEP
excelexcel-2007vba
user1737389
user1737389

Reputation: 53

How to transfer (cut & paste) a picture from a worksheet to another

I have the picture temporarily placed and hidden in Sheet1. I chose this method because I prefer not to have the picture stored locally on my drive.

Then, I have a modeless UserForm with a button that should transfer the picture from Sheet1 to whichever is the ActiveSheet at the moment.

So far I have the following code to hide the picture prior to closing the workbook: ('On Error' is in there because, depending on the user, the picture will not always end-up in Sheet1)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim tempWs As Variant
    Call HidePic
End Sub

Private Sub HidePic()
    Dim tempWs As Variant
    On Error Resume Next
    For Each tempWs In ThisWorkbook.Worksheets
        tempWs.Pictures.Visible = False
    Next
End Sub

Next, the following code works but only in Sheet1 (where, in this case, the picture is stored). When I switch to another sheet and then hit the button on the UserForm; whatever data stored in "A1" is removed but no picture is placed/pasted.

Private Sub Pic_Click()
    Dim tempWs As Variant
    On Error Resume Next
    For Each tempWs In ThisWorkbook.Worksheets
        tempWs.Pictures.Visible = True
        tempWs.Pictures.Select
    Next

    Selection.Cut
    ActiveSheet.Range("A1").Activate
    ActiveSheet.Paste
End Sub

Additional info: I used "Select/Selection.Cut" instead of the shorter(and faster?) ".Cut" only since, for some reason using Cut only doesn't place the picture in "A1"

Upvotes: 1

Views: 2235

Answers (2)

brettdj
brettdj

Reputation: 55672

If your picture on the first sheet was called Picture 1 you could try this

Private Sub Pic_Click()
Dim objPic As Shape
On Error Resume Next
Set objPic = Sheets(1).Shapes("Picture 1")
On Error GoTo 0
If objPic Is Nothing Then Exit Sub
objPic.Copy
ActiveSheet.Range("a1").PasteSpecial
objPic.Delete
End Sub

Upvotes: 2

Christian Sauer
Christian Sauer

Reputation: 10889

You are iterating through all worksheets with your For Each tempWs In ThisWorkbook.Worksheets construction. this should work:

Dim TempSht as Worksheet
set tempSht = Thisworkbook.sheets(1)
tempsht.Pictures.Visible = True
tempsht.Pictures.Select

Upvotes: 0

Related Questions