-1

Current Code:

Const cName = "ChartDummy"
Const rName = "Availability"
Const iconSource = "C:\user.name\defaulfolder\"
'/* Availability is a named Range for this example but can be any valid range */
'/* UF_EmoTrans is a Userform with and Image Control named I_Console */

Dim oCh As ChartObject, chImg As String
Dim Ch As Object

Application.DisplayAlerts = False
With Sheet2
  '.Activate
  chImg = iconSource & "availIcon.jpg"

  Set oCh = .ChartObjects.Add(370, 14, 180, 277.2): oCh.Name = cName
  .Range(rName).CopyPicture xlScreen, xlBitmap

  'oCh.Activate
  oCh.Chart.Paste
  oCh.Chart.Export chImg, "JPG"
  '/* I call this procedure at UserForm_Initialize */
  UF_EMOTrans.I_Console.Picture = LoadPicture(chImg)
  oCh.Delete

End With

My current code works when I run it in break mode (using F8 and stepping through each line).
It exports the Range as image(through chartobject) and loads it to the userform just fine.

How Image Control Looks

Now, when I run this in execution mode (loading the form), it doesn't create the same result.
It seems the oCh.Chart.Paste method didn't fire and so no image was paste within the chart.
Once exported, it produces a picture with plain white background.

How it looks when in not in break mode

So my question is, why is that? Why does it behave differently?

P.S.: It will work if the chart is activated (but you will need to activate the sheet first) prior pasting (commented lines).
But I just want to know if this is one of those oddities that Excel have that we just have to live with.

L42
  • 19,427
  • 11
  • 44
  • 68
  • This isn't a complete example. It's tough to give any advice without knowing more about these undeclared variables and (what I assume to be) named ranges(?)... for example, **what is `.Range("Availability")`** that you're trying to copy? If it's a **cell range**, afaik you can't paste an image of it. If it's a **chart**, then you're referring to it incorrectly as a range. See [mcve] – ashleedawg Oct 06 '18 at 04:02
  • @ashleedawg `If it's a cell range, afaik you can't paste an image of it. If it's a chart, then you're referring to it incorrectly as a range` [Yes you can](https://stackoverflow.com/q/16143877/2685412). – L42 Oct 06 '18 at 04:13
  • 1
    I, along with others, have experienced the same problem. I believe it's some sort of bug, as suggested [here](https://social.msdn.microsoft.com/Forums/en-US/736eeb5f-7f21-4006-87ee-7748f3c83175/chartexport-creates-corrupt-files-randomly?forum=isvvba). So you'll need to activate the chart to ensure it works properly. – Domenic Oct 06 '18 at 04:51
  • I'm not sure which answer you're referring to on that page. Your code is pasting the data, not an image, into the chart. This is an unsupported "hack", hence why you won't find any current documentation from Microsoft. Perhaps if you back up and explain your end goal, theres an alternative solution. – ashleedawg Oct 06 '18 at 04:51
  • @Domenic Oh I see. So you have to really `Activate` everything then to make it work. Thanks Domenic. – L42 Oct 06 '18 at 04:56
  • @ashleedawg You can actually copy and paste ranges as picture manually (Other Paste Options) and by code using `CopyPicture` method. And you can paste images on Charts (not only data) both manually and by code as well. You can even paste linked images. As for my end goal, I want to display my target range in a Userform with all its formatting. And so what I did is copy the range as image (using CopyPicture Method), paste it in the Chart (to make use of its Export Method), saved it as image and then load it in the image control. – L42 Oct 06 '18 at 05:05
  • @ashleedawg Not sure if this is a 'hack' though cause you can actually do it manually. But if you have an alternative route to come up with my end goal, that would be sweet. – L42 Oct 06 '18 at 05:06

1 Answers1

0

When you show Userform, show userform modeless.

or When your proceure include userform.show , the image worked well.

UF_EMOTrans.Show (vbModeless)

or

UF_EMOTrans.Show (0)
Dy.Lee
  • 7,527
  • 1
  • 12
  • 14
  • Thanks for the reply, I am already loading my form modeless. You can't get away with *Activating* the chart object. – L42 Oct 08 '18 at 22:13