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

Hui · Jan 12, 2023

D Coker said:
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

In the original post the data was discrete and it was quite permissible for events to overlap
In the original post the locations of the shapes represented times and movements

You could loop through the shapes and adjust them, but you'll need some logic to define how far?

Then you loose the story that the shape is portraying

I'd recomend using transparent shapes so that at least you can see through upper level shapes

D Coker · Jan 12, 2023

Hui said:
In the original post the data was discrete and it was quite permissible for events to overlap
In the original post the locations of the shapes represented times and movements

You could loop through the shapes and adjust them, but you'll need some logic to define how far?

Then you loose the story that the shape is portraying

I'd recomend using transparent shapes so that at least you can see through upper level shapes

Thanks for the reply, Hui.

This is what I am trying to avoid.
The shapes are good. The text boxes are overlapping is the problem.

D Coker · Jan 12, 2023

D Coker said:
Thanks for the reply, Hui.

This is what I am trying to avoid.
The shapes are good. The text boxes are overlapping is the problem.

View attachment 82410

If I could somehow figure out how to loop through and check if each text box overlaps a previous text box and move them slighly within a loop until it no longer overlaps, I would believe that would fix this problem. I can't seem to figure the formula or vba to check that specific condition. I have a macro that outputs each text box and their properties into a sheet.

Indication	Name	Top	Left	Bottom	Right	Overlaps
MD-001	TextBox 166	242.7190552	247.4750366	252.5794487	274.1076355
EC-001	TextBox 170	228.0419617	226.1600037	237.9023552	249.1675625	TRUE
ARCB-001	TextBox 173	330.2850342	327.6050415	340.1454277	360.1245689	FALSE
ARCB-002	TextBox 176	344.0281067	290.1050415	353.8885002	322.6245689	FALSE
ARCB-003	TextBox 179	276.9030762	295.25	286.7634697	327.7695274	TRUE
ARCB-004	TextBox 183	230.157959	327.5899963	240.0183525	360.1095238	TRUE
ARCB-004	TextBox 185	642.2260742	327.5899963	652.0864677	360.1095238	TRUE
ARCB-005	TextBox 188	220.9655914	398.3705444	230.825985	430.8900719	TRUE
ARCB-006	TextBox 3903	273.1739502	350.7049561	283.0343437	383.2244835	TRUE
ARCB-007	TextBox 3906	255.1739349	337.2049561	265.0343285	369.7244835	TRUE
ARCB-008	TextBox 3909	247.7086639	508.684967	257.5690575	541.2044945	TRUE
ARCB-009	TextBox 3912	375.6461487	502.684967	385.5065422	535.2044945	TRUE
ARCB-010	TextBox 3915	276.9030762	494.434967	286.7634697	526.9544945	TRUE
ARCB-011	TextBox 3918	505.6252747	494.434967	515.4856682	526.9544945	TRUE
ARCB-012	TextBox 3921	388.7781067	520.1599731	398.6385002	552.6795006	TRUE
ARCB-013	TextBox 3925	221.7497711	536.6300049	231.6101646	569.1495323	TRUE
ARCB-013	TextBox 3927	644.0906372	521.6300049	653.9510307	554.1495323	TRUE
ARCB-014	TextBox 3930	401.2086487	528.9799805	411.0690422	561.4995079	TRUE
ARCB-015	TextBox 3933	342.1635437	652.460022	352.0239372	684.9795494	TRUE
ARCB-016	TextBox 3936	342.1635437	326.1199951	352.0239372	358.6395226	TRUE
POR-001	TextBox 3939	336.6288147	227.0599976	346.4892082	255.7272415	TRUE
UNC-001	TextBox 3942	310.0350342	359	319.8954277	388.4649601	TRUE
UNC-002	TextBox 3945	421.0975647	329.7950439	430.9579582	359.260004	TRUE
LIN-001	TextBox 3948	401.2086487	438.5750427	411.0690422	463.9807129	TRUE
LIN-002	TextBox 3951	325.3822937	652.460022	335.2426872	677.8656921	TRUE

D Coker · Jan 12, 2023

D Coker said:
If I could somehow figure out how to loop through and check if each text box overlaps a previous text box and move them slighly within a loop until it no longer overlaps, I would believe that would fix this problem. I can't seem to figure the formula or vba to check that specific condition. I have a macro that outputs each text box and their properties into a sheet.

Indication Name Top Left Bottom Right Overlaps
MD-001 TextBox 166
242.7190552
247.4750366
252.5794487
274.1076355
EC-001 TextBox 170
228.0419617
226.1600037
237.9023552
249.1675625
TRUE
ARCB-001 TextBox 173
330.2850342
327.6050415
340.1454277
360.1245689
FALSE
ARCB-002 TextBox 176
344.0281067
290.1050415
353.8885002
322.6245689
FALSE
ARCB-003 TextBox 179
276.9030762
295.25
286.7634697
327.7695274
TRUE
ARCB-004 TextBox 183
230.157959
327.5899963
240.0183525
360.1095238
TRUE
ARCB-004 TextBox 185
642.2260742
327.5899963
652.0864677
360.1095238
TRUE
ARCB-005 TextBox 188
220.9655914
398.3705444
230.825985
430.8900719
TRUE
ARCB-006 TextBox 3903
273.1739502
350.7049561
283.0343437
383.2244835
TRUE
ARCB-007 TextBox 3906
255.1739349
337.2049561
265.0343285
369.7244835
TRUE
ARCB-008 TextBox 3909
247.7086639
508.684967
257.5690575
541.2044945
TRUE
ARCB-009 TextBox 3912
375.6461487
502.684967
385.5065422
535.2044945
TRUE
ARCB-010 TextBox 3915
276.9030762
494.434967
286.7634697
526.9544945
TRUE
ARCB-011 TextBox 3918
505.6252747
494.434967
515.4856682
526.9544945
TRUE
ARCB-012 TextBox 3921
388.7781067
520.1599731
398.6385002
552.6795006
TRUE
ARCB-013 TextBox 3925
221.7497711
536.6300049
231.6101646
569.1495323
TRUE
ARCB-013 TextBox 3927
644.0906372
521.6300049
653.9510307
554.1495323
TRUE
ARCB-014 TextBox 3930
401.2086487
528.9799805
411.0690422
561.4995079
TRUE
ARCB-015 TextBox 3933
342.1635437
652.460022
352.0239372
684.9795494
TRUE
ARCB-016 TextBox 3936
342.1635437
326.1199951
352.0239372
358.6395226
TRUE
POR-001 TextBox 3939
336.6288147
227.0599976
346.4892082
255.7272415
TRUE
UNC-001 TextBox 3942
310.0350342
359
319.8954277
388.4649601
TRUE
UNC-002 TextBox 3945
421.0975647
329.7950439
430.9579582
359.260004
TRUE
LIN-001 TextBox 3948
401.2086487
438.5750427
411.0690422
463.9807129
TRUE
LIN-002 TextBox 3951
325.3822937
652.460022
335.2426872
677.8656921
TRUE

The current formula is incorrect I believe.
Formula within cell N3: =IF(I3<>"",IF(COUNTIFS($J$2:J2,">="&J3,$L$2:L2,">="&J3)+COUNTIFS($K$2:K2,"<="&K3,$M$2:M2,">="&K3)>0,TRUE),"")

Snippet showing referenced cells in last row's formula.

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

Hui

Excel Ninja

D Coker

Member

D Coker

Member

D Coker

Member