Results 1 to 3 of 3
Thread: Copy myRange to form
2010-03-12, 13:26 #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)
2010-03-13, 21:42 #2
- Join Date
- Mar 2004
- Manning, South Carolina
- Thanked 1,472 Times in 1,340 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
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 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
2010-03-14, 19:37 #3
- 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