VBA Code to get text array from shapes

Blueorangez

New Member
Joined
Dec 15, 2016
Messages
14
I have a sheet with a grid of rectangle shapes with text in them.

The grid is 6 columns across and 17 down.

The shapes can be moved around in their columns so this needs to be dynamic.

I need to get the text or the shape name or shape properties from all the shapes currently in that row and paste them to cells on the right side so I can perform calculations on data.

1581555305488.png


so what I'm trying to do is each of these shapes has a different value,
I can use the text or name the shape with its value.

but I need to get the info into the grid on the right so I can do some calculations

I see this working as a module with a run button, so the user can move the shapes in their columns throughout the day than when they are ready to calculate the hit run. The values move over to the right and excel formulas add the relevant info up for them.


if it's easier it could be on a new sheet, if there is VBA to get arrays from all shapes on a sheet and paste them as text to another sheet.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Add your button and put this code behind it
- it outputs both the shape name and the shape text
- amend Button999
- amend to suit your requirements after testing

VBA Code:
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
        If shp.Name <> "Button999" Then
            With shp.TopLeftCell
                On Error Resume Next
                .Offset(, 8) = shp.Name
                .Offset(, 16) = shp.TextFrame.Characters.Text
            End With
        End If
    Next shp
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top