Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009
    Thanked 0 Times in 0 Posts
    I'd like to copy myRange on UserForm1
    This what I have so far(...

    Dim strFile As String
    Dim pic As ????

    Load UserForm1 'Load Hidden
    b.Range("A1:H" & n).CopyPicture 'myRange copied as a picture
    b.Paste Destination:=b.Range("A2") 'picture object
    Set pic = ???? 'not sure what to set it to - can't paste directly on form
    strFile = "c:\temp.gif"
    pic.Export Filename:=strFile, FilterName:="GIF"

    UserForm1.Image1.Picture = LoadPicture(strFile)

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Manning, South Carolina
    Thanked 1,609 Times in 1,453 Posts
    The code below will successfully load an image into an image object on a form.
    The two commented lines were my attempt to get the image within code however I got an "Object Required" error on the Set imgCopy line.
    Also the LoadPicture function requires a FileSpec. It appears that somehow you'll have to paste into a file then load it as below.

    Good Luck...Please post your final results for us all...Thanks!


    Sub UserForm_Initialize()
      Dim imgCopy As Image
      'Set imgCopy = Range("A1:D6").CopyPicture
      'If imgCopy = Null Then MsgBox "It's Empty", vbOKOnly, "Testing"
      imgMyRange.Picture = LoadPicture("G:\BEKDocs\Excel\Test\Bruce.bmp")
    End Sub
    Upon some additional research {check out CopyPicture Method in VBA Help} the CopyPicture Method does not appear to apply to Form Objects.
    I've tried various options but none but the above will get a picture into a Form.



    I tried my suggestion above about writing to a file but no go. Here's the code just in case someone else might know how to fix it.
    Sub UserForm_Initialize()
      Dim imgCopy As DataObject
      Dim zCurPath As String
      zCurPath = CurDir() & "\imgFile.bmp"
      Worksheets("Sheet1").Range("A1:D6").CopyPicture xlScreen, xlBitmap
      Set imgCopy = New DataObject
      If imgCopy Is Nothing Then MsgBox "No DATA", vbOKOnly, "Status:"
      Open zCurPath For Output As #1
      Write #1, imgCopy          '*** Returns Run-time error 438
                                 '*** Object doesn't support this property or method
      Close #1
      imgMyRange.Picture = LoadPicture(zCurPath)
    End Sub
    BTW: The picture is on the clipboard as I steped through the code and managed to manually paste the range picture into the worksheet.

    May the Forces of good computing be with you!


    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Cambridge, UK
    Thanked 3 Times in 3 Posts
    I do not think there is a way to save the clipboard to file from VBA without using a windows API.
    Similarly, I do not think you can Paste to a standard image control on a form.

    IF that is the case, then you need an API to save the clipboard to file,
    so you can then load it with a temporarily saved image file.

    Attached file will copy current selection to clipboard, save it with an API and then load it into an image control on a user form.

    You will need to change the file paths in the basCopy module to make it work

    Attached Files Attached Files

Posting Permissions

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