Linking Text Box to Excel Range using VBA

Linking Text Box to Excel Range (Excel 2007)

Here are the steps

1. Insert Text Box from Developer --> Insert



2. Set the LinkedCell of the textbox from Properties window



3. Text Typed in Embedded Text Box will be reflected on sheet range



This way you can create a simple data entry form

Here is the way to do the same using VBA code

Sub Insert_TextBOX_OLE()

Dim oOLETB As OLEObject ' Ole Object Text Box

Dim oWS As Worksheet ' Work sheet

On Error GoTo Err_OLE

' ---------------------------------------------

' Coded by Shasur for www.vbadud.blogspot.com

' ---------------------------------------------

oWS = ActiveSheet

oOLETB = oWS.OLEObjects.Add("Forms.TextBox.1")

oOLETB.Name = "MySampleTextBox"

oOLETB.Height = 20

oOLETB.Width = 100

oOLETB.Top = Range("D2").Top

oOLETB.Left = Range("D2").Left

oOLETB.LinkedCell = "$I$2"

oOLETB.Object.Text = "VBADUD Sample"

' ---------------------------------------------

' Destroy Object

' ---------------------------------------------

Finally:

If Not oOLETB Is Nothing Then oOLETB = Nothing

If Not oWS Is Nothing Then oWS = Nothing

' ---------------------------------------------

' Error Handling

' ---------------------------------------------

Err_OLE:

If Err <> 0 Then

MsgBox(Err.Description)

Err.Clear()

GoTo Finally

End If

End Sub