VBA - Formatting text inside shapes

[liwi2]

Hi All,
I am not sure where to start with this one. I never worked on code where shapes would be involved and I am not sure if there is solution for what I need.

Basically I have Master sheet with multiple shapes : arrows, rectangles etc. The number of shapes vary from week to week.

What I need is to copy master sheet to new sheet in new workbook,remove all rectangles (that's the easy part) and the part which I don't know how to crack is formatting text inside arrow shapes.
Text is ie: xxxx: yyyyy
zzzzzzzzzzzz
aaaaaaaaaa
bbbbbbbbbb
ccccccccccc

Ideally I need to remove everything till ":", 2nd line and then 4th and 5th line. I could do with removing ALL 1st, 2nd line and then 4th and 5th line if needs be.

I hope someone can help me with above.

Thanks a million

 

[pgc01]

Shape types are 33 and 34.

In that case we can use it to select the shapes where we want to edit the text.
Replace the first procedure

Sub EditAllArrowsText()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
    If shp.AutoShapeType = msoShapeLeftArrow Or shp.AutoShapeType = msoShapeRightArrow Then _
        EditShpText shp
Next shp

" Subscript out of range" on line
s = Split(s, ":", 2)(1)

This means that the text in that shape does not have a ":".
Please confirm.

 

[liwi2]

HI,
Apologies for the delays. Thank you for help and explanation

I implemented above solution however am still getting an error The strange thing is that all arrow shapes have ":"

 

[pgc01]

Hi

When you get the error check which shape the code is processing.
This will help you debug the error.

For that shape examine the text and make sure the ":" is there.

So
1 - when the code stops which shape is being processed?
2 - examine the text in that shape

 

[liwi2]

Sorry, I am very much amateur and I don't know if I there is any other way to check it. I cannot see any shape selected when get the error. I would be grateful for direction

 

[pgc01]

When you get the message "Subscript out of range" you have the options End and Debug. Press Debug and the code stops and you can examine the data.

In this case, when the code stops, check in the Locals window the

shape name (expand the shp object and see the name property)
the text in the shape (expand the txtR object and see the Text property


If you don't have the Locals window visible, in the menu of the VBEditor View->Locals Window

Please try.

 

[liwi2]

Thanks for your patience.

I actually used Debug.Print instead and realised that the owner of the workbook had hidden rows and one arrow indeed didn't have ":" :/ sorry. I should have think of this one first.

So this error shouldn't be relevant as I need only visible data with arrows which have ":" to be transferred to new workbook.

I think I will be able to workout the rest. If I come across any problems with the code I will let you know.

Thank you so much for your support. Much appreciated

 

[pgc01]

You're welcome. I'm glad it helped.