How to link a textbox to a cell Excel Macro

If you’re putting together some kind of diagram in Excel, you may want to have the ability to click on a shape or textbox and have it automatically jump to another location, like a cell. I have always believed it is faster to mass produce things then manually delete what you don’t need rather than manually creating textboxes, and this is another example of that. I would run a macro to create 50 linked textboxes and then if I only use 30 I can quickly delete the left over 20.

For example, the macro creates ten Textboxes and then links them to cells A1 through A10 using an automatically created hyperlink. The two most important expressions we will be using are:

To add and position a new Textbox using VBA:

.AddTextbox(Orientation, Left, Top, Width, Height)

To add a hyperlink to a textbox using VBA:

.Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)

If you’re not familiar with programming VBA macros in Excel, check out my quick start guide here. Listed below is the complete code with my comments to help walk you through it:

Sub CreateTextBoxes()

Dim i, d As Integer

‘d will be used as spacing to separate the textboxes so they are not created on top of one another

d = 10

‘perform the loop 10 times and create 10 textboxes. Could change this to be more or into a user input box

For i = 1 To 10

'create textboxes in a vertical column with equal spacing using d

       ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 150, 20 + d, 35, 25).Select

   

'add formula to textboxes so the textbox displays the text in the cell

    Selection.Formula = "=$A$" & i

   

'create hyperlink from textbox to cell, assumes sheet name is Sheet1

       ActiveSheet.Shapes.Range(Array(i)).Select

    ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:="", SubAddress:="Sheet1!A" & i

   

    d = d + 30

  

Next

End Sub

How to link a shape to a cell Excel Macro Video

If you put some values in cells A1 – A10 and then run this macro, it will create 10 textboxes, and when you click on the textbox it will jump to the location of the appropriate cell.  Download this sample file here.