strange chart/object problem

Corman · Mar 21st, 2002, 10:26 AM

Hello. I hope someone can help me with this one.

I have an object floating over a chart. The object consists of several connected rectangles, each containing textboxes. The individual portions making up the object are grouped into one object. Basically it is a legned for the x-axis.

The object is used to describe different sections of the xy-scatter chart. The rectangles are connected at certain specific x-axis values.

My problem is that when I save the worksheet and re-open it, excel has automatically resized the object for some reason (thus the sides of the rectangles are no longer at set up at the correct x-axis values). The height of the object is not changed, but the width is changed from 14.57 to 14.32 (within format...object...size).

The object is locked, and the worksheet is protected. Even the aspect ratio of the object is locked, so one dimension should not be able to change without the other one changing. I'm stumped.

It only happens on saving. It looks just the way I want it after I enter the data that is plotted on the chart, but not after it is opened after saving (it needs to be opened by a couple of different people in the course of completing the worksheet, so it needs to be saved and re-opened).

Is this something I am doing to cause this? Could it be a known issue within excel (XL97)?

ANY help is greatly appreciated!

Thanks,
Corey D.

Corman · Mar 21st, 2002, 10:59 AM

Hi again.

I'm still trying to fix the problem, and have tried a run-around of it. I recorded a macro which goes into the format...object...size area and changes the object to the proper width/height. I then also move it to the correct location.

But, when I try to run the macro using vba from an ok button in a user form, it gives me the following error:

Run-time error 91
Object variable or With block variable not set

The code from the macro is as follows:

ActiveSheet.ChartObjects("Chart 11").Activate
Selection.ShapeRange.Height = 45.07
Selection.ShapeRange.Width = 413.01
Selection.ShapeRange.IncrementLeft -7.49
Selection.ShapeRange.IncrementTop -0.31

Any ideas why this doesn't work? Do I need to define the Height/Width/Increment... as variables first?

Thanks,
Corey D.

Corman · Mar 21st, 2002, 11:30 AM

Is there anyway to specify the left and top distances for a ShapeRange object using vba?

For instance, I can specify the following using a recorded macro:

Selection.ShapeRange.Height = 45.07
Selection.ShapeRange.Width = 413.01
Selection.ShapeRange.IncrementLeft -8.49
Selection.ShapeRange.IncrementTop -0.31

But can you specify the actual left and top values (not just increment values)?

Thanks,
Corey D.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 21st, 2002, 10:26 AM	#1
Corman New Member Join Date: Mar 2002 Posts: 23	Hello. I hope someone can help me with this one. I have an object floating over a chart. The object consists of several connected rectangles, each containing textboxes. The individual portions making up the object are grouped into one object. Basically it is a legned for the x-axis. The object is used to describe different sections of the xy-scatter chart. The rectangles are connected at certain specific x-axis values. My problem is that when I save the worksheet and re-open it, excel has automatically resized the object for some reason (thus the sides of the rectangles are no longer at set up at the correct x-axis values). The height of the object is not changed, but the width is changed from 14.57 to 14.32 (within format...object...size). The object is locked, and the worksheet is protected. Even the aspect ratio of the object is locked, so one dimension should not be able to change without the other one changing. I'm stumped. It only happens on saving. It looks just the way I want it after I enter the data that is plotted on the chart, but not after it is opened after saving (it needs to be opened by a couple of different people in the course of completing the worksheet, so it needs to be saved and re-opened). Is this something I am doing to cause this? Could it be a known issue within excel (XL97)? ANY help is greatly appreciated! Thanks, Corey D.

Mar 21st, 2002, 10:59 AM	#2
Corman New Member Join Date: Mar 2002 Posts: 23	Hi again. I'm still trying to fix the problem, and have tried a run-around of it. I recorded a macro which goes into the format...object...size area and changes the object to the proper width/height. I then also move it to the correct location. But, when I try to run the macro using vba from an ok button in a user form, it gives me the following error: Run-time error 91 Object variable or With block variable not set The code from the macro is as follows: ActiveSheet.ChartObjects("Chart 11").Activate Selection.ShapeRange.Height = 45.07 Selection.ShapeRange.Width = 413.01 Selection.ShapeRange.IncrementLeft -7.49 Selection.ShapeRange.IncrementTop -0.31 Any ideas why this doesn't work? Do I need to define the Height/Width/Increment... as variables first? Thanks, Corey D.

Mar 21st, 2002, 11:30 AM	#3
Corman New Member Join Date: Mar 2002 Posts: 23	Is there anyway to specify the left and top distances for a ShapeRange object using vba? For instance, I can specify the following using a recorded macro: Selection.ShapeRange.Height = 45.07 Selection.ShapeRange.Width = 413.01 Selection.ShapeRange.IncrementLeft -8.49 Selection.ShapeRange.IncrementTop -0.31 But can you specify the actual left and top values (not just increment values)? Thanks, Corey D.