Gantt Chart - Bar Text to be created after the Bar is over

[ajoshi76]

The below code is executed after the shape (Gantt chart bar is created) to update the text (Task description).... The output is that from the left margin of the bar the task description is populated... But i would like to have the task description tagged after the bar is ended. I am attaching the output of the current code - any help appreciated.

>>> use code - tags <<<

If (startDate <= Cells(CurrentRow, 10).Value And EndDate >= Cells(CurrentRow, 10).Value) Or Cells(CurrentRow, 8).Value = "Bar" Or Cells(CurrentRow, 8).Value = "Pointed Bar" Then

With shapePP
.Fill.ForeColor.RGB = RGB(X, Y, Z)
'for bars descriptions are within the bar shape
'if there is no code number or name of milestone "--" sign is skipped
If Cells(CurrentRow, 8).Value = "Bar" Or Cells(CurrentRow, 8).Value = "Pointed Bar" Then
If (Cells(CurrentRow, 5).Value = "" Or Cells(CurrentRow, 6).Value = "") Then
.TextFrame.TextRange.Text = Cells(CurrentRow, 5).Value & Cells(CurrentRow, 6).Value
End If

If (Cells(CurrentRow, 5).Value <> "" And Cells(CurrentRow, 6).Value <> "") Then
.TextFrame.TextRange.Text = Cells(CurrentRow, 5).Value & " - " & Cells(CurrentRow, 6).Value
End If
End If

.Line.ForeColor.RGB = RGB(115, 115, 115)
.TextFrame.TextRange.Font.Name = FontName
.TextFrame.TextRange.Font.Color.RGB = RGB(10, 10, 10)
.TextFrame.TextRange.Font.Size = FontSize - 1

If Cells(CurrentRow, 12).Value = "yes" Then
.TextEffect.FontBold = msoTrue
End If

.Line.Weight = 0.1
.TextEffect.Alignment = msoTextEffectAlignmentLeft
.TextFrame.WordWrap = msoFalse

End With

 

[vletm]

ajoshi76
You could add Your texts to own '2nd textframes' after those Your 'textframes'.
Based You snapshot and part of Your code, it would be some challenges to give exact other solution.
With a sample file, there would be more ideas to do this.

 

[ajoshi76]

I added

>>> use code - tags <<<

TextFrame2.MarginLeft = RowHeight * ShapeSize *3

The smaller gantt chart bars it worked fine. But for Larger bars it doesnt work...

 

[vletm]

ajoshi76
... above won't solve Your challenge, Your text is still inside of those bars.
... .marginleft should be same as width of that shape ... if try to do that way.

 

[ajoshi76]

Attaching my workbook

 

[ajoshi76]

The whole code worked except of the text on the bar

 

[vletm]

ajoshi76
You seems to do something manually ... why do won't use Excel's Chart?
Here one sample ...

but if You would like to do as You've started ...
try to do as I wrote You could add Your texts to own '2nd textframes' after those Your 'textframes'.
like in this sample's 3rd serie.

>> I won't start to modify Your code.

 

[ajoshi76]

The point was to use Excel data to create a Powerpoint gantt (via code) to send to leadership.
How does one determine the length of the bar ? Because i tried margin left etc. nothing worked

 

[vletm]

ajoshi76
Wasn't Your Point as You've written: Gantt Chart - Bar Text to be created after the Bar is over ?
You've created those Shapes slidePP.Shapes.AddShape ... from there You can see its Width - okay?
(( as tried to give a hint with #4 reply )) Use that value for .TextFrame.MarginLeft ... and if needs add 10 or something.
It could work ... if it allows to write outside of those shapes.

OR as written few times >> try to do as I wrote You could add Your texts to own '2nd textframes' after those Your 'textframes'.

Ps. any etc not normally work

 

[ajoshi76]

okay thanks

.Textframe.MarginLeft = Width *1.1
The code crashed with an error - after getting the text fixed on the bar. so it worked on the bar and when it reached the milestone it crashed.

 

[vletm]

ajoshi76
What is value of Your used Width?
That not look like correct ... Width.
Of course, You uses same value as Shapes.Width or how?
Is it CategoryWidth or something else?
>> I even tested Your code ... it won't work for me at all ... endless loop? <<

 

[ajoshi76]

.TextFrame.MarginLeft = DiagramWidth * ((Cells(CurrentRow, 11).Value - Cells(CurrentRow, 10).Value) / (EndDate - startDate)) * 1.2

Not sure why it is going in endless loop as it works fine on my machine

 

[vletm]

ajoshi76
Why You won't use ready solved variable values ( + eg 10) ?
It's good that works fine with You ... then at least You could use it.

 

[ajoshi76]

Thanks It worked with the + value suggestion.
What excel version are you using? Though it shouldnt matter. Just curious. As it should not go in unlimited loop. Did you edit the data sheet with your data ? Do check the date format .... that causes a lot of issues

 

[vletm]

I'm using Excel 2019.
That code was s l o w . I tried to follow it few minutes ...
I would use Excel's chart as I showed - of course, that would need or use some code too.

 

[ajoshi76]

oh thats very odd - as my entire data was populated in the powerpoint gantt in few seconds.