VBA Code to get text array from shapes

Blueorangez

New Member
Joined
Dec 15, 2016
Messages
13
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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,119
Messages
5,628,799
Members
416,340
Latest member
PJB1102

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
Top