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

Creating a Command Button on Sheet using Excel VBA

Adding an OLE Object (Command Button) to a Worksheet using Excel VBA

Sub Create_Command_Button_2007()

'

' Creates a Command button and Positions it

' Written by Shasur for http://vbadud.blogspot.com

Dim oOLE As OLEObject

' Add a Command Button

oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=220, Top:=40, Height:=30, Width:=120)

oOLE.Interior.Color = vbRed

' Move and Size with cells

oOLE.Placement = XlPlacement.xlMoveAndSize

oOLE.Object.Caption = "Click Me..."

End Sub

Each OLEObject object represents an ActiveX control or a linked or embedded OLE object.

An ActiveX control on a sheet has two names: the name of the shape that contains the control, which you can see in the Name box when you view the sheet, and the code name for the control, which you can see in the cell to the right of (Name) in the Properties window. When you first add a control to a sheet, the shape name and code name match. However, if you change either the shape name or code name, the other is not automatically changed to match.