Copying text from a shape

[rj]

Hi All,

Is there any way I can copy the text from a shape in a worksheet to a
cell on the same worksheet?
Im looking for a formula, a macro would be last resort.

Thanx in advance..

 

[Ken Johnson]

Hi rj,
As far as I know there is no formula that can do that.
If you select the shape containing the required text then run the
following macro it will show an inputbox asking you to select the
destination cell.
Then, after you click OK, that shape's text will appear in the
destination cell you selected.
You will have to make sure that when you select the shape that the
flashing I-bar does not appear inside the shape. Macros can't be run
while Excel is in edit mode.
So, click the shapes border, not its interior, then run this macro...

Public Sub ShapeCaptionToCell()
Dim strShpText As String
Dim rngDestination As Range
On Error GoTo NOTSHAPE
strShpText = Application.selection.Caption
Set rngDestination = Application.InputBox(prompt:="Select the
destination cell", Type:=8)
rngDestination.Value = strShpText
NOTSHAPE:
End Sub

To get the code in place...
1) Copy
2) press Alt + F11 to get into the VBA Editor
3) Go Insert>Module
4) Paste the code into the new module

Ken Johnson

 

[Ken Johnson]

Hi rj,
So that you don't have any problems caused by the break in the 6th
line, copy and paste this version...

Public Sub ShapeCaptionToCell()
Dim strShpText As String
Dim rngDestination As Range
On Error GoTo NOTSHAPE
strShpText = Application.selection.Caption
Set rngDestination = Application.InputBox _
(prompt:="Select the destination cell", _
Type:=8)
rngDestination.Value = strShpText
NOTSHAPE:
End Sub

Also, when you select the destination range it can be bigger than one
cell and can even be a non-contiguous range (selected while holding
down Ctrl).

Ken Johnson

 

[rj]

Thanx Ken

 

[Ken Johnson]

You're welcome rj.
Thanks for the feedback.
Ken Johnson