Tuesday, July 29, 2008

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


No comments:

Post a Comment

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.