Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Feb 2009
    Posts
    30
    Thanks
    0
    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)
    UserForm1.Show

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 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!

    RG

    Code:
    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.

    RG

    More...

    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.
    Code:
    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
      imgCopy.GetFromClipboard
      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.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    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

    [attachment=88324:mac_CopyImage.xls]
    Attached Files Attached Files
    Andrew

Posting Permissions

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