Positioning Shapes on a Chart

P

pistonbroke

New Member
Joined
Jan 15, 2003
Messages
49
Hello all. I want to be able to Add Shapes to an Excel Chart and position them at absolute references top and left based on the number of series that are plotted (dynamic - different number of series each time). I have the general idea but because the shapes are textboxes and contain some long strings, it won't allow all the shapes to be positioned where i want them.. I think Excel knows how long the text box will be in "normal" orientation (Horizontal text), and the end would exceed the chart area, even though i am using "msoTextOrientationUpward" that would fit all the labels on the plot (I can move them manually and they all fit)

2022 Summary Plot Add Textbox Problem.xlsm
MNOPQRSTUVWXYZ
2(Sample Text - Can be more complex)
3
42022 Round 1TextBoxes (Event Names)
52022 Round 2LeftTopWidthHeight
62022 Round 305121016
72022 Round 4
82022 Round 5
92022 Round 6
102022 Round 7
112022 Round 8
122022 Round 9
132022 Round 10
142022 Round 11
152022 Round 12
162022 Round 13
172022 Round 14
182022 Round 15
192022 Round 16
202022 Round 17
212022 Round 18
222022 Round 19
232022 Round 20
242022 Round 21
252022 Round 22
262022 Round 23
272022 Round 24
282022 Round 25
292022 Round 26
302022 Round 27
312022 Round 28
322022 Round 29
332022 Round 30
342022 Round 31
352022 Round 32
362022 Round 33
372022 Round 34
382022 Round 35
39
40
41
42
43
All Specs Plotter 2022


VBA Code: 
Sub AddTextBoxes()

Dim L As Double
Dim T As Double
Dim W As Double
Dim H As Double
Dim TX As String

ActiveSheet.ChartObjects("Cht1").Activate

For Each wShape In ActiveChart.Shapes
wShape.Delete
Next wShape

Dim theChartObj As ChartObject
Set theChartObj = Worksheets("All Specs Plotter 2022").ChartObjects("Cht1")

Dim theChart As Chart
Set theChart = theChartObj.Chart

Dim theTextBox As Shape

'Event Label
For s = 4 To Cells(4, 13).End(xlDown).Row

L = ((s - 3) * 25) + 20
T = Cells(6, 18).Value
W = Cells(6, 19).Value
H = Cells(6, 20).Value
TX = Cells(s, 13)

Set theTextBox = theChart.Shapes.AddTextbox(msoTextOrientationUpward, L, T, W, H) 'Left | Top | Width | Height

With theTextBox.TextFrame.Characters
    .Text = TX
    With .Font
        .Name = "Tahoma"
        .Size = 10
        .Bold = msoFalse
    End With
End With

theTextBox.Name = "TB" & s - 3
'theTextBox.Rotation = 270
'theTextBox.IncrementLeft -(Cells(6, 19).Value / 2)

theTextBox.TextFrame.AutoSize = True

Next s

End Sub


Hope i've pasted the sheet and code ok !!!!! Thanks for any help / thoughts All....
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
The Plot (stripped of a lot of other stuff) looks like this, Data series are not linked to the info i want to show in the TextBoxes and there are 3 series occupying the same x-Axis area for each of the 35 Series (so Legend won't help me) Note the issue starts when trying to place series 28 and Higher ? Thanks, Kevin
 

Attachments

  • TextBox Placement Issue.jpg
    TextBox Placement Issue.jpg
    153.9 KB · Views: 15
Upvote 0
pistonbroke said:
Hello all. I want to be able to Add Shapes to an Excel Chart and position them at absolute references top and left based on the number of series that are plotted (dynamic - different number of series each time). I have the general idea but because the shapes are textboxes and contain some long strings, it won't allow all the shapes to be positioned where i want them.. I think Excel knows how long the text box will be in "normal" orientation (Horizontal text), and the end would exceed the chart area, even though i am using "msoTextOrientationUpward" that would fit all the labels on the plot (I can move them manually and they all fit)

2022 Summary Plot Add Textbox Problem.xlsm
MNOPQRSTUVWXYZ
2(Sample Text - Can be more complex)
3
42022 Round 1TextBoxes (Event Names)
52022 Round 2LeftTopWidthHeight
62022 Round 305121016
72022 Round 4
82022 Round 5
92022 Round 6
102022 Round 7
112022 Round 8
122022 Round 9
132022 Round 10
142022 Round 11
152022 Round 12
162022 Round 13
172022 Round 14
182022 Round 15
192022 Round 16
202022 Round 17
212022 Round 18
222022 Round 19
232022 Round 20
242022 Round 21
252022 Round 22
262022 Round 23
272022 Round 24
282022 Round 25
292022 Round 26
302022 Round 27
312022 Round 28
322022 Round 29
332022 Round 30
342022 Round 31
352022 Round 32
362022 Round 33
372022 Round 34
382022 Round 35
39
40
41
42
43
All Specs Plotter 2022


VBA Code: 
Sub AddTextBoxes()

Dim L As Double
Dim T As Double
Dim W As Double
Dim H As Double
Dim TX As String

ActiveSheet.ChartObjects("Cht1").Activate

For Each wShape In ActiveChart.Shapes
wShape.Delete
Next wShape

Dim theChartObj As ChartObject
Set theChartObj = Worksheets("All Specs Plotter 2022").ChartObjects("Cht1")

Dim theChart As Chart
Set theChart = theChartObj.Chart

Dim theTextBox As Shape

'Event Label
For s = 4 To Cells(4, 13).End(xlDown).Row

L = ((s - 3) * 25) + 20
T = Cells(6, 18).Value
W = Cells(6, 19).Value
H = Cells(6, 20).Value
TX = Cells(s, 13)

Set theTextBox = theChart.Shapes.AddTextbox(msoTextOrientationUpward, L, T, W, H) 'Left | Top | Width | Height

With theTextBox.TextFrame.Characters
    .Text = TX
    With .Font
        .Name = "Tahoma"
        .Size = 10
        .Bold = msoFalse
    End With
End With

theTextBox.Name = "TB" & s - 3
'theTextBox.Rotation = 270
'theTextBox.IncrementLeft -(Cells(6, 19).Value / 2)

theTextBox.TextFrame.AutoSize = True

Next s

End Sub


Hope i've pasted the sheet and code ok !!!!! Thanks for any help / thoughts All....
Click to expand...
Ok, found a workaround which isn't pretty but fixes this issue - basically just redefine the TextBox positioners AFTER the Autosize operation. Works great now ! Thanks for everyone who took a look..

Rich (BB code): 
'Variables from Work Sheet
L = ((s - 3) * G) + Cells(43, 15).Value
T = Cells(47, 14).Value
W = Cells(48, 14).Value
H = Cells(49, 14).Value
TX = Cells(s, 8)

Set theTextBox = theChart.Shapes.AddTextbox(msoTextOrientationUpward, L, T, W, H) 'Left | Top | Width | Height

With theTextBox.TextFrame.Characters
    .Text = TX
    With .Font
        .Name = "Tahoma"
        .Size = 10
        .Bold = msoFalse
    End With
End With

theTextBox.TextFrame.AutoSize = True
theTextBox.Left = L '<<<<<Redefine left position
theTextBox.Height = H '<<<<<Redefine height
theTextBox.Top = T '<<<<<Redefine top
theTextBox.Name = "TB" & s - 3
 
Last edited by a moderator:
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

A
  • Question Question
Loan Tracker with prior/missed payments
Replies
0
Views
112
aurelius33
A
P
  • Solved
Calculate time spent in hourly interval
Replies
2
Views
226
phiero21
P
H
  • Question Question
Copy Tables & Charts from Excel to Powerpoint, Keep source formatting and change position
Replies
0
Views
385
h899
H
VinceF
  • Question Question
Circular Reference Error
Replies
3
Views
395
VinceF
VinceF
S
  • Solved
Need to compare True and False data
Replies
2
Views
428
sksanjeev786
S

Forum statistics

Threads
1,224,826
Messages
6,181,192
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top