• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

ajoshi76

Member
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 <<<
Code:
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
 

Attachments

  • Screenshot 2021-07-05 at 1.34.13 AM.png
    Screenshot 2021-07-05 at 1.34.13 AM.png
    252.1 KB · Views: 9
Last edited by a moderator:
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.
 
I added

>>> use code - tags <<<
Code:
TextFrame2.MarginLeft = RowHeight * ShapeSize *3
The smaller gantt chart bars it worked fine. But for Larger bars it doesnt work...
 
Last edited by a moderator:
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
You seems to do something manually ... why do won't use Excel's Chart?
Here one sample ...
Screenshot 2021-07-05 at 13.50.59.png
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.
 

Attachments

  • PlanOnPage - Modified.xlsm
    167.5 KB · Views: 5
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
 
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
 
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.
 
Last edited:
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? <<
 
.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 :(
 
Last edited by a moderator:
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.
 
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
 
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.
 
Back
Top