0

I have some old VB6 code that adds a shape to an Excel chart. 

    ' add a label to display the results
    Set BoxShape = Chart.Shapes.AddLabel(1, ScaleToPointsX(series, x), ScaleToPointsY(series, y) - 20, 1, 1)
    BoxShape.TextFrame.Characters.Text = sText

This works fine on some machines, but on others the size is not set correctly. On investigation this appears to be because the Autosize property is not always defaulting to True.

The code has been running for years on various versions of Excel. I have two PCs both running Excel 2007 (exact same build version) one of them works fine (Autosave defaults to True) and the other the shape does not appear (Autosize defaults to False). The one that works is Windows 7 and the one that doesn't is Windows 10, but I doubt that is the issue.

I think I can fix the problem by explicitly setting TextFrame.Autosize to True when the shape is created, but I am curious as to what defines how this value is set initially. Is there some global setting in Excel that affects this?

Phil Preen
  • 589
  • 5
  • 20
  • This code is VBA, not VB6. VB6 has to send Excel commands through an Excel.Application variable using Automation, and I don't see that here. You might want to change the tag from VB6 to VBA. – BobRodes Mar 28 '16 at 04:29
  • This is VB6 code, although the exact same problem occurs with the same code in VBA. Automation is through the Chart variable which is a reference to an Excel Chart object, sorry if that wasn't obvious. I didn't think it was necessary to show the code for obtaining this reference. – Phil Preen Mar 29 '16 at 16:44
  • As I recall it (and it has been a while), you have to have a reference to an application object in VB6, e. g. xl.Chart.Shapes.AddLabel(etc.), where xl is a reference to an Excel.Application object. This is how I taught it in my classes (of which I did many). However, if you are saying that this code runs fine as is in the Visual Studio 6 IDE for Visual Basic, then I stand corrected and didn't realize/don't remember that you can directly reference the Chart object. p.s. I don't know Excel well enough to answer your question, sorry. – BobRodes Mar 29 '16 at 16:53

0 Answers0