CODEWITHSUNDEEP
vbaexcel
Craig Stanley
Craig Stanley

Reputation: 75

Is it possible to use a shape formula as cell reference?

I am using Excel 2007. I have a worksheet that contains many shapes. Each shape is linked to a cell on another spreadsheet (i.e., '=Data!$F$5') in order to dispay some text in each shape.

Now, I would like to use the formula in the shapes as a starting point to reference other cells from the worksheet. The idea behind this is to run a macro when the user clicks on the shape to give them additional information in a message box.

I've tried making a string from "ActiveShape.DrawingObject.Formula", but haven't had any success. Does anyone know how to do this or suggest another way of accomplishing this?

Any help is greatly appreciated.

Upvotes: 1

Views: 1652

Answers (1)

chancea
chancea

Reputation: 5958

Within the macro that you assigned to the shape(s). You can use ActiveSheet.Shapes(Application.Caller).name to get the name of the shape and then ActiveSheet.Shapes.Range(Array(name)) to get the shape reference. Then you can select it and use Selection.formula to get the formula

Copy this and paste it into your macro for the shape and you can see how it works:

Dim name As String
Dim formula As String
name = ActiveSheet.Shapes(Application.Caller).name

ActiveSheet.Shapes.Range(Array(name)).Select
formula = Selection.formula

MsgBox formula

Note: I did try and just do formula = ActiveSheet.Shapes.Range(Array(name)).formula but vba was unhappy with me. So I had to stick to the .Select and Selection.formula

Upvotes: 1

Related Questions