Format a Text Box shape with Automatic Colour Gradient Stops

[Pete Mccann]

Hello, is it possible to format a Text Box to simulate a % completion progress bar by automatically selecting one (or two) gradient stops. The attached file shows how I do this manually at present.

 

[Nebu]

Hi

May be something like this?

THanks

 

[Pete Mccann]

Hi Nebu - very nice. I didn't think of adding a small chart to the text box. This creates the right view and shows the information clearly. I've made a small modification to the chart (attached). Do you think it would be possible to add the Start and Finish dates automatically to the X-Axis of the chart (instead of having the % complete as the X-axis). In the attached I've added these dates as Text boxes linked to the source. Many thanks.

 

[Belleke]

Or something like this?

 

[Pete Mccann]

Thank you Belleke, this is a very nice solution too. My original idea was to have a floating text box that is part of a chart so that when chart data was updated, the Text box Progress bar was also updated. I can imagine other scenarios where your report form could be used so thank you for your help and ideas. Best regards.

 

[Nebu]

Hi:

May be overlap the graphs and show the axis as attached?

Thanks

 

[Pete Mccann]

Hi Nebu, thanks for this message. I had thought of overlapping two charts but when Excel calculates the range of dates for the X-Axis, the start and finish dates do not correspond to the two dates I was looking for. See image attached.

 

[Nebu]

Hi:

Find the attached. I have customised the axis

Thanks

 

[Pete Mccann]

Hi Nebu. Thank you for spending time on this question. If the Start Date or the Finish date changes then the dates in the chart axes do not change. Unfortunately this is a scenario that will happen.

 

[Nebu]

Hi:

This macro version will automatically adjust the axis when you make the changes to dates.

Thanks

 

[Pete Mccann]

Thanks Nebu - this is perfect.