Using VBA to plot data into a custom "chart" using msoShapeRectangle

[D Coker]

Hello,

I have a table of items that need to be plotted and labeled on a grid. The items that need to be plotted need to be displayed as a
"msoShapeRectangle" with specific background color dependent on the type of item.
Start of Rectangle = Distance from zero reference
Length of Rectangle = Length
Top of Rectangle = Orientation (Clock)
Width of Rectangle = Width

 

[Hui]

D Cocker

Firstly, Welcome to the Chandoo.org Forums

Why wouldn't you just do this as a chart ?



see attached file for details:

 

[D Coker]

Unfortunately, the length and width need to be dependant on the data values listed. See below. If this is possible with a chart, then I'm all game!

 

[Hui]

Ouch

That would be possible using VBA, But I would use a chart for the background and grids and the add shapes & text to that with VBA
You can have a chart with axis and grids and no data

Unfortunately I don't have spare time available at present to tackle this type of job

 

[D Coker]

Hui,

Your input is much appreciated!

 

[Hui]

How's this for a start


Should be able to tidy it up tomorrow

 

[D Coker]

That's a great start!!

 

[Hui]

I labelled this "Chart from Hell", but it has actually turned out to be "Chart with Much Satisfaction"

Please find attached a first pass at what I think you want


The VBA is fairly well labelled and so you can see things that you may want to change, like the Text Offset/Size and Rectangle Border etc, as well as seeing what each block of code does

I have centered the text over the rectangle, but that can be changed

Please have a look and any feedback appreciated

 

[Hui]

One issue I spotted is what do you want to happen to data that bleeds past the edges?

 

[D Coker]

You are the MAN!!

I don't think we should have an issue with the data going off of the chart as long as the chart x-axis can be changed...say from -6.0,6.0 to 0,12 or even 15...

The distance, length, and width should be in bounds if the data was entered correctly by the user.

 

[Hui]

I will need to change the code if you want to change the axis scales

I'll post a new version shortly

 

[D Coker]

That's no problem, if I can superimpose the chart over the indication map and make it transparent, I wouldn't even have to worry with the axis

 

[Hui]

see revised workbook
This will now allow you to change the X Axis scale

 

[Hui]

I have added an option in the Code to supress the Left Overlap plotting
I will fix the right overlap tomorrow
You can toggle it on/off as you need in the code

I have also fixed an error in the Clear Plot that would delete the plot if you pressed it twice, whoops

 

[D Coker]

Thank you!

I have attached a revised version with minor changes.

The y-axis will be 12:00 to 11:59, clock position on a cylinder.
-I hope this makes sense.


Also.....

If the cylinder diameter (in inches) is changed, this would then change the height dimension of the rectangle as a ratio of the total circumference (plot area height)
-Could this be added?

Is there a way to send each text box to the front after all rectangles are plotted?
Could the text box length be independent from the length of the indication? If the indication is too small, then the text box text becomes vertical.

If this chart could be superimposed to the Indication Map Tab inside the "Need Help" workbook, that would be awesome!

If the rectangle went past the top of the chart, could it wrap the remainder from the bottom?

Eventually, I need to use the Indication Table and Indication Map worksheets as the final IO areas.

I know this process is so time consuming, and I greatly appreciate your help!
If you need to bow out at any time, I completely understand.

 

[Hui]

The y-axis will be 12:00 to 11:59, clock position on a cylinder.
-I hope this makes sense. Yes, its 12 Hrs instead of 24, Fixed


Also.....

If the cylinder diameter (in inches) is changed, this would then change the height dimension of the rectangle as a ratio of the total circumference (plot area height)
-Could this be added? It already was it uses the Width column, Thats why MD010 is so tall


Is there a way to send each text box to the front after all rectangles are plotted?
Yes, Completed

Could the text box length be independent from the length of the indication? If the indication is too small, then the text box text becomes vertical.

If this chart could be superimposed to the Indication Map Tab inside the "Need Help" workbook, that would be awesome! Done

If the rectangle went past the top of the chart, could it wrap the remainder from the bottom? Possible, but later

Eventually, I need to use the Indication Table and Indication Map worksheets as the final IO areas. Done

I know this process is so time consuming, and I greatly appreciate your help!
If you need to bow out at any time, I completely understand.

see attached file

ps: Muchas Gracias

.

 

[Hui]

I hope your sitting down?


Note that I have had to change the X & Y labels, but we can discuss

see attached file

Let me know what you think

 

[D Coker]

Hui,

I think it's absolutely amazing!!!!!

I added a column for a converted width and changed the code to pull from the new column. This way the width can be entered in inches, then converted to minutes based on the circumference.

I also changed the amin and amax to be pulled from the input on the actual map so that this can be changed easily by the user.


see attached if you like
_____________________________________________________________

Is there a way to now insert the macros and sheets into an existing workbook without affecting the macros, etc?


You are, indeed, an excel ninja!

 

[Hui]

Please see revised file

Changes

1. You have option to disable boundary overlap on left and right edges now
2. Fixed Textbox width for narrow rectangles so they don't wrap
3. Removed the helper column "converted width" you added and shifted the maths into the code. You hadn't copied the formulas down and so it didn't plot the rectangles, this way that can't happen
4. I have shifted the code to the Worksheet Module, that way if the worksheet is copied the code will go with it, but it still relies on the name and data structure of the "Indication Table" worksheet being the same

I don't think it requires much more work?

 

[Hui]

Left Right overlap On


Same Data, Left Right overlap Off


Vertical Rectangle wrap is not affected

 

[D Coker]

I can't thank you enough!




could code be added for the instance that the clock position for the indication between 12:00 and 12:59 plot correctly? It seems that the code will not plot the box in this instance. Any insight would be great!

 

[D Coker]

Hey I think i figured it out!

If ot >= 0.5 Then
ot = ot - 0.5
End If

 

[Hui]

That’s correct
The Y Axis goes from 0 to 0.5
or alternatively enter 12:15 as 12:15 am
but it will be displayed as 0:15

also you can simplify the code as

If ot >= 0.5 Then ot = ot - 0.5

 

[Peter Bartholomew]

@Hui
Nice piece of work. I think the option of using shapes in combination with charts to extend functionality is generally neglected. Something that can be fun is to reverse the process. Instead of using the figure to illustrate data, the figure is drawn or modified (the MouseMove event is pretty useful in that context) and the data is built from the figure.

In the attached, the lamp and lamp holder moved on the 'bench'. The x,y coordinates of the light source changed to match and the path of diffracted rays were recalculated using Solver.

 

[D Coker]

Hui,

Revisiting this chart - I am curious if we can get the text boxes not to overlap by looping through the list of textboxes and their positions. I posted this in a separate thread recently. If I can get this approach to return true or false and shift each text box as they are plotted, it should work I believe.

Separate Link