Log In
Register
Home
April 25, 2020
Offline
This is driving me round the bend!
I am attaching a sample file, which is taken from my actual file that is a"Wholesale Customer Order Form". The file, in a nutshell, allows a wholesale customer to create an order by using a userform with 2 List Views, the first is a list of available Items from a Category chosen via Option Buttons (5 categories and a total of approx 1,500 Items). Items can then be selected using check boxes in the List view quantities are then entered for each checked Item and all checked Items moved to the 2nd List View which forms the actual complete Order. Another category can then be chosen and Items from that added to the Order. Once complete a 'Submit' Command Button transfers the Order List View to a Table on the worksheet which the gets emailed.
That is a very simplified idea of the process (for example quantities can be edited at various stages and Items from a "Previous Month's Best Sellers" list added to the order, in addition to the above).
In the user form first List View if an Item is selected, as opposed to being checked, then an Image of that Item is pulled from a web site and displayed in another userform.
The sample file attached has the Image UserForm and code to get the Image from the web site and load it into the Image Control in that userform.
In the actual file the Table of Items and associated Image URLs has 1,500 rows and the code goes to the row that corresponds to the Item Name clicked in the ListView. In the Sample file I have included just a few of them and hard coded the Item Name as "Infiniti Red Dice Set".
If you click the button the Image UserForm shows but the Image is empty.
If you look at the code and put a break on the line "oCht.Chart.Export sPath" then click the button, then move the cursor back one line up to "oCht.Chart.Paste" and step through that line again then the Image appears in the Chart Object and running the rest of the code loads the image correctly into the Image UserForm Image Controls and all is fine.
I have tried repeating the "oCht.Chart.Paste" line but that still only works if the code run is blocked and a step back performed. I added code that goes back to the "oCht.Chart.Paste" line automatically and repeats it, but that also did not load the image.
I did notice that there was a slight delay after stepping through that line again manually before the image appeared so I tried adding an "Application.Wait......" for a 2 second wait, still not working. Same thing if I us "DoEvents" rather than "Application.Wait".
Finally I tried Activating the Chart Object before loading the Image into the Chart Object Chart then exporting it. That loaded the Image immediately but exported only a "Windows default Image" to the Image UserForm!
Placing the images in the file is not a option as there are currently 1,500 of them, more to come in the future, and it would turn a 200KB file into a 200MB file!
Any help or other suggestions greatly appreciated as I have run out of ideas!. Thanks.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Instead of pasting an image to chart, try using the .AddPicture method:
' Add the image to the chart
oCht.Activate
Set shp = oCht.Chart.Shapes.AddPicture(imgPath, _
msoFalse, msoCTrue, _
10, 10, 100, 100) ' Adjust position and size as needed
' Wait for the image to be fully rendered
DoEvents
Application.Wait (Now + TimeValue("0:00:02")) ' Wait for 2 seconds
Or you can try looping until image is rendered:
' Activate the chart
oCht.Activate
' Paste the image into the chart
imgLoaded = False
Do Until imgLoaded
oCht.Chart.Paste
DoEvents
Application.Wait (Now + TimeValue("0:00:01")) ' Wait for 1 second
If Not oCht.Chart.Pictures.Count = 0 Then imgLoaded = True
Loop
April 25, 2020
Offline
Thanks Catalin.
I had done a load more research over the weekend and found that it is not necessary to even use a Chart Object, the image can be copied to the clipboard then, after some manipulation of the clipboard, the image can be pasted directly into the Image control of the second user form. No need for Application.Wait, Do Events or loops to check if the image is loaded into a Chart Object.
I eventually got the clipboard manipulation code working by piecing together code snippets got from various sources and then modifying it until it eventually worked!
I am attaching the file with the code working. Click the button to show a user form with a ListView (just one column in this sample), click a Name and the image of that item will show.
Trusted Members
October 17, 2018
Offline
#If VBA7 Then
Private Declare PtrSafe Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Integer) As Long
Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Private Declare PtrSafe Function GetClipboardData Lib "user32" (ByVal wFormat As Integer) As Long
Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
Private Declare PtrSafe Function OleCreatePictureIndirect Lib "olepro32.dll" (PicDesc As uPicDesc, RefIID As GUID, ByVal fPictureOwnsHandle As Long, IPic As IPicture) As Long
Declare PtrSafe Function CopyEnhMetaFile Lib "gdi32" Alias "CopyEnhMetaFileA" (ByVal hemfSrc As Long, ByVal lpszFile As String) As Long
Declare PtrSafe Function CopyImage Lib "user32" (ByVal handle As Long, ByVal un1 As Long, ByVal n1 As Long, ByVal n2 As Long, ByVal un2 As Long) As Long
#Else
Private Declare Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Integer) As Long
Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function GetClipboardData Lib "user32" (ByVal wFormat As Integer) As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function OleCreatePictureIndirect Lib "olepro32.dll" (PicDesc As uPicDesc, RefIID As GUID, ByVal fPictureOwnsHandle As Long, IPic As IPicture) As Long
Declare Function CopyEnhMetaFile Lib "gdi32" Alias "CopyEnhMetaFileA" (ByVal hemfSrc As Long, ByVal lpszFile As String) As Long
Declare Function CopyImage Lib "user32" (ByVal handle As Long, ByVal un1 As Long, ByVal n1 As Long, ByVal n2 As Long, ByVal un2 As Long) As Long
#End If
April 25, 2020
Offline
Good tip Hans, thanks.
I was so pleased to get it working I did not think about 32 & 64 bit systems! 
Trusted Members
Moderators
November 1, 2018
Offline
@Hans
Most of those VBA7 declarations are wrong for 64 bit. All handles and pointers need to be LongPtr not Long. You can't just put PtrSafe into a declaration and leave it that 😉
Trusted Members
October 17, 2018
Offline
@Velouria, thaks for pointing that out but I did that in the past first times many years ago but ran into issues with the LongPtr declartion.
I set these al back to long with the PrtSafe and no more problems, also the people I helped and wrote code for have unitl now not run into problems.
Trusted Members
Moderators
November 1, 2018
Offline
That's either pure luck or they simply haven't reported it. Getting API calls wrong tends to lead to applications terminating without warning, and those are wrong. For example hWnd as Long should always be hWnd as LongPtr for compatibility.
