Link cell to Excel form

Question

In excel, we insert a shape, and we can associate it with the cell value by selecting a shape and typing a cell in the formula bar. I want to know: how to do this programmatically. how

For Each shape As Excel.Shape In workshet.Shapes 'is there something like shape.Formula or any method from which I can do the above task. Next 

Here is what I want to do programmatically

These were the days I'm looking for him. I really need help, thanks Alo.

Answer 1

You do not need to use Select to apply formulas to shapes. Choices should be avoided whenever possible as the code bloats and may have unintended consequences - for example, triggering

Instead, you can work with the Formula directly using a DrawingOBject, see FormApp , which will also allow you to manipulate an existing formula (for example, add 6 cells to A2 to make them A8, A12 - A18, etc.). The second FormAdd code FormAdd performs this setup; it works both with cell addresses and range names

 Sub FormApp() Dim Shp As Shape For Each Shp In ActiveSheet.Shapes 'formula Shp.DrawingObject.Formula = "=A1" 'range name Shp.DrawingObject.Formula = "=RangeName" Next End Sub Sub FormAdd() Dim Shp As Shape Dim rng1 As Range For Each Shp In ActiveSheet.Shapes Set rng1 = Nothing If Len(Shp.DrawingObject.Formula) > 0 Then On Error Resume Next Set rng1 = Range(Shp.DrawingObject.Formula) On Error GoTo 0 If Not rng1 Is Nothing Then Shp.DrawingObject.Formula = "=" & rng1.Offset(6, 0).Address End If Next End Sub 

Answer 2

The easiest option is to first select the form programmatically, and then apply the formula

 Dim Shp as Shape For each Shp in ActiveSheet.Shapes Shp.Select Selection.Formula = "=A1" Next 

Or it will be an error if the form on the sheet does not support the .Formula property