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)
Hope i've pasted the sheet and code ok !!!!! Thanks for any help / thoughts All....
| 2022 Summary Plot Add Textbox Problem.xlsm | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |||
| 2 | (Sample Text - Can be more complex) | |||||||||||||||
| 3 | ||||||||||||||||
| 4 | 2022 Round 1 | TextBoxes (Event Names) | ||||||||||||||
| 5 | 2022 Round 2 | Left | Top | Width | Height | |||||||||||
| 6 | 2022 Round 3 | 0 | 512 | 10 | 16 | |||||||||||
| 7 | 2022 Round 4 | |||||||||||||||
| 8 | 2022 Round 5 | |||||||||||||||
| 9 | 2022 Round 6 | |||||||||||||||
| 10 | 2022 Round 7 | |||||||||||||||
| 11 | 2022 Round 8 | |||||||||||||||
| 12 | 2022 Round 9 | |||||||||||||||
| 13 | 2022 Round 10 | |||||||||||||||
| 14 | 2022 Round 11 | |||||||||||||||
| 15 | 2022 Round 12 | |||||||||||||||
| 16 | 2022 Round 13 | |||||||||||||||
| 17 | 2022 Round 14 | |||||||||||||||
| 18 | 2022 Round 15 | |||||||||||||||
| 19 | 2022 Round 16 | |||||||||||||||
| 20 | 2022 Round 17 | |||||||||||||||
| 21 | 2022 Round 18 | |||||||||||||||
| 22 | 2022 Round 19 | |||||||||||||||
| 23 | 2022 Round 20 | |||||||||||||||
| 24 | 2022 Round 21 | |||||||||||||||
| 25 | 2022 Round 22 | |||||||||||||||
| 26 | 2022 Round 23 | |||||||||||||||
| 27 | 2022 Round 24 | |||||||||||||||
| 28 | 2022 Round 25 | |||||||||||||||
| 29 | 2022 Round 26 | |||||||||||||||
| 30 | 2022 Round 27 | |||||||||||||||
| 31 | 2022 Round 28 | |||||||||||||||
| 32 | 2022 Round 29 | |||||||||||||||
| 33 | 2022 Round 30 | |||||||||||||||
| 34 | 2022 Round 31 | |||||||||||||||
| 35 | 2022 Round 32 | |||||||||||||||
| 36 | 2022 Round 33 | |||||||||||||||
| 37 | 2022 Round 34 | |||||||||||||||
| 38 | 2022 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 SubHope i've pasted the sheet and code ok !!!!! Thanks for any help / thoughts All....
