macro or function to list text box entries

[llomax83]

I have a sheet that contains text boxes with text entered in them. I would like to be able to list the text from each text box in a column in a second sheet. Is there any way to automate this so I don't have to go through and manually reenter the text? Thanks!

[kev_]

Try this

Run from sheet containing the textboxes
Replace "OtherSheetName" with name of 2nd sheet

Put in general module

Please Login or Register  to view this content.

[llomax83]

No luck with that...

What may be causing the problem is each one of my text boxes is actually 9 shapes grouped together. I have 8 small boxes covered by 1 large box to give me more snap points. Would this be a problem? I've added a small example of what I have and what I'm trying to do. Thanks!

[kev_]

Sorry away from my PC so cannot look at your file

[kev_]

In the meantime perhaps you could try this (untested)
Run from sheet containing the shapes.

Please Login or Register  to view this content.

[llomax83]

Still no luck. Run from Sheet1 with the shapes and in the example attached below Sheet3 set as ws2. Let me know what you think if you have a chance to look at it once you're back to your PC-thanks for the suggestion regardless!

[kev_]

Ok
Will look on Sunday if no-one else has solved the puzzle

[kev_]

Try this in the attached workbook first
- to test the macro, click on sheet "Shapes" and then click back onto sheet "OtherSheetName"
(deactivating sheet "Shapes" runs the macro)
- "OtherSheetName" will now contain expected list
- add another shape and then click on "OtherSheetName" and list should refresh

You wanted text adding to list when new shape created
- it is easiest to trigger macro to run when sheet "Shapes" is deactivated

Macro:
- runs through all shapes and group child shapes
- checks to see (via MATCH function or could use vLookup) whether text associated with each shape exists already in sheet "OtherSheetName"
- if MATCH fails then text is added in sheet "OtherSheetName"

(NOTE - the test for duplication is the actual text itself - this assumes that each shape contains different text)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[llomax83]

Wow! - Just ran this in the example, and that's exactly what I was trying to accomplish. Even the duplicate test is something I was going to have to accomplish with a filter so that is perfect. I haven't had a chance to import the code over to my actually sheet yet, but it seems to be working perfectly on the example. Thanks again!

[kev_]

Yoy are welcome , thanks for the reps

Duplicates:
Another way to deal with duplicates would be to copy all found text across without testing for a match and then apply Remove Duplicates function to column A

Looking above, I have just spotted an error in the my code - I will post an amended and slightly tidied up version shortly

To correct the error in the code
replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

[kev_]

Here is tidied-up version of the code
I took the opportunity to make use of FIND instead of MATCH
- which is put in a separate sub-routine to avoid repeating the same bit of code
All variables are now declared at the top of the module to make them available to all procedures in the module

Please Login or Register  to view this content.

[kev_]

from PM

Thanks again for the help. I'm having some problems implementing the code into my spreadsheet.
The first tab on my project is "Tree" and the second tab is labeled "List".
I've
- copied the code onto "Tree" with the shapes,
- changed the name of ws1 = Sheets("Tree") and ws2 = Sheets("List"), and
- saved
But when I go back to Tree to run the macro nothing happens.
Any advice?

What causes the macro to run automatically in my example file is the VBA below
It is placed in the SHEET module of the sheet containing the shapes, in your case sheet "Tree"
It runs automatically when the sheet is de-activated and calls procedure GroupedItemsTextValues

Please Login or Register  to view this content.

To put code in sheet module:

Sheet_module.jpg

3 Paste code into the module which appears

[llomax83]

Finally had a chance to play around with this some more. I was able to make it work by manually running the macro from the code page. For some reason it won't run automatically when switching from the "shape" sheet to the "list" sheet. This actually works out OK with how I'm using this sheet.

Something else I found though was that "comments" are treated just like shapes and will pick these up too. Is there anyway to easily filter out comments? (This isn't critical as it's easy to identify them in the list and delete them. Thanks again!

[kev_]

You did not tell me which version of my code you settled on (post#8 or tidied up version in post#11)
This is the amended version of post#11
This applies the code only to shape type msoGoup and works with the test data you supplied - but you may be using other types of shapes in the "live" file.

Please Login or Register  to view this content.

Another method
If the above is excluding some other type of shape that you want then you could instead simply wrap the relevant bit of the code with an IF statement, like this:
'

Please Login or Register  to view this content.

[kev_]

For some reason it won't run automatically when switching from the "shape" sheet to the "list" sheet.

4 typical possible reasons why this may be happening

1. The VBA is in the wrong module
- it must be placed in the sheet module of the sheet that is being deactivated
(in this case the sheet containing your shapes)

2. The procedure has the wrong name
- it must be named Private Sub Worksheet_Deactivate()

3. It is not calling the correct procedure
- Call GroupedItemsTextValues verify that this exactly matches the name of the other procedure
(unlikely - VBA should throw an error and tell you)

4. "Events" are disabled for some reason
- is there another bit of code doing the disabling?
- it can happen if VBA stops running part way through procedure and events have not been re-enabled
- run the code below to re-enable events

Please Login or Register  to view this content.

[kev_]

from PM

The comments all refer to one or more of the shapes in a list. The duplicate check takes care of a lot of the comment boxes and the others all have an [ENTER] between the text. I'd like to be able to filter these if possible by deleting any entry that has a [ENTER] input.

Is there anyway you know how to filter and delete these extra entries?

1. To ignore everything in that shape's text frame:
Insert this line:

Please Login or Register  to view this content.

Before this line:

Please Login or Register  to view this content.

2. To remove only the text after "Enter"
Insert this line:

Please Login or Register  to view this content.

Before this line:

Please Login or Register  to view this content.

[llomax83]

The 2nd line of code to remove only the text after "Enter" works, but I when I run the 1st option to remove the entire text in a box with an "Enter" input, the code runs for a second and returns nothing. If possible I'd like to be able to filter out the entire text of any shape that has an "Enter". Here is what I have...

Please Login or Register  to view this content.

Thanks again for all your help!

[kev_]

deleted by Kev

[kev_]

Try this:

Please Login or Register  to view this content.

[llomax83]

Just tried it and getting an error:

Compile error:
Sub or Function not defined

The "Call CopyTest" line is highlighted.

I didn't want to attach my actual spreadsheet, but I've updated my original example with something similar to what I'm trying to accomplish. In the example, I have six shapes with text. Four of the shapes have a comment box running behind them. All of the comment boxes are either duplicates of text from a shape or has an "Enter" (Object 5).

Thanks!