I am trying to add a text box form to an excel spreadsheet and I am having no
luck with the code. Even the recorded code doing the action, will not play
back correctly.

I need assistance with the syntax to do the following...

Add a text box (from the Controls toolbar)
To a certain cell location
Link this box to a named range

I am stumped and stuck.

Thanks in advance...

I got the code below from the macro recorder and I made a few modifications.
It will create a textbox on cell B1. The textbox will be 50x18 pixels in
size. The textbox is then linked to a range named as "LinkedCell".

With ActiveSheet
.OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=.Range("B1").Left,
Top:=.Range("B1").Top, Width:=50, _
Height:=18).Select
Selection.LinkedCell = "LinkedCell"
End With



--

Hope that helps.

Vergel Adriano


"jayklmno" wrote:

I am trying to add a text box form to an excel spreadsheet and I am having no
luck with the code. Even the recorded code doing the action, will not play
back correctly.

I need assistance with the syntax to do the following...

Add a text box (from the Controls toolbar)
To a certain cell location
Link this box to a named range

I am stumped and stuck.

Thanks in advance...

This worked ok for me:

Option Explicit
Sub Macro2()
Dim OLEObj As OLEObject
Dim myAddr As String
Dim myPos As String
Dim wks As Worksheet

myAddr = "A1" 'or whatever range name you want
myPos = "C1:E9"

Set wks = ActiveSheet

With wks.Range(myPos)
Set OLEObj = .Parent.OLEObjects.Add(ClassType:="Forms.TextBox.1 ", _
Link:=False, DisplayAsIcon:=False, _
Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)

OLEObj.LinkedCell = .Parent.Range(myAddr).Address(external:=True)

End With

End Sub

jayklmno wrote:

I am trying to add a text box form to an excel spreadsheet and I am having no
luck with the code. Even the recorded code doing the action, will not play
back correctly.

I need assistance with the syntax to do the following...

Add a text box (from the Controls toolbar)
To a certain cell location
Link this box to a named range

I am stumped and stuck.

Thanks in advance...

--

Dave Peterson

Both of these solutions work to create the text box where I want it, but in
order for me to have the box pull in the correct range, in this case a
formatted table,
I need to change the formula linked to the object...

which is normally
=EMBED("Forms.TextBox.1","")

to
=Liq_Table

The problem seems to be in the recorded code, once the object is created and
selected, Selection.Formula = "=Liq_Table" doesn't work.

Any suggestions?

Did you try changing the linked cell so that it points at that range.

myAddr = "A1"
becomes
myAddr = "liq_table"



jayklmno wrote:

Both of these solutions work to create the text box where I want it, but in
order for me to have the box pull in the correct range, in this case a
formatted table,
I need to change the formula linked to the object...

which is normally
=EMBED("Forms.TextBox.1","")

to
=Liq_Table

The problem seems to be in the recorded code, once the object is created and
selected, Selection.Formula = "=Liq_Table" doesn't work.

Any suggestions?

--

Dave Peterson

Yes,

myAddr = "Liq_Table"
myPos = "Cells(NextRow + 1, 2)"

When I run it this way, I get a Run-time error '1004': "Method 'Range' of
object'_Worksheet' failed" here...

With wks.Range(myPos)



"Dave Peterson" wrote:

Did you try changing the linked cell so that it points at that range.

myAddr = "A1"
becomes
myAddr = "liq_table"



jayklmno wrote:

Both of these solutions work to create the text box where I want it, but in
order for me to have the box pull in the correct range, in this case a
formatted table,
I need to change the formula linked to the object...

which is normally
=EMBED("Forms.TextBox.1","")

to
=Liq_Table

The problem seems to be in the recorded code, once the object is created and
selected, Selection.Formula = "=Liq_Table" doesn't work.

Any suggestions?

--

Dave Peterson

If I change the way I address the cells to "B47", It works, but the box is
empty and the formula when I select it is still the =EMBED, not my range
reference.

"jayklmno" wrote:

Yes,

myAddr = "Liq_Table"
myPos = "Cells(NextRow + 1, 2)"

When I run it this way, I get a Run-time error '1004': "Method 'Range' of
object'_Worksheet' failed" here...

With wks.Range(myPos)



"Dave Peterson" wrote:

Did you try changing the linked cell so that it points at that range.

myAddr = "A1"
becomes
myAddr = "liq_table"



jayklmno wrote:

Both of these solutions work to create the text box where I want it, but in
order for me to have the box pull in the correct range, in this case a
formatted table,
I need to change the formula linked to the object...

which is normally
=EMBED("Forms.TextBox.1","")

to
=Liq_Table

The problem seems to be in the recorded code, once the object is created and
selected, Selection.Formula = "=Liq_Table" doesn't work.

Any suggestions?

--

Dave Peterson

myPos expects a string--not a formula.

If you want to use a range:

Option Explicit
Sub Macro2()
Dim OLEObj As OLEObject
Dim myAddr As String
Dim myPos As Range
Dim NextRow as long
Dim wks As Worksheet

myAddr = "A1" 'or whatever range name you want

Set wks = ActiveSheet

With wks
'something here that defines NextRow
Nextrow = 12 'whatever.
set myPos = .Cells(NextRow + 1, 2)
with myPos
Set OLEObj = .Parent.OLEObjects.Add(ClassType:="Forms.TextBox.1 ", _
Link:=False, DisplayAsIcon:=False, _
Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)

OLEObj.LinkedCell = .Parent.Range(myAddr).Address(external:=True)
end with
End With

End Sub

If you're using a textbox from the Drawing toolbar, you can use the formula
technique. But I thought you wrote you were using the textbox from the control
toolbox toolbar (so don't use that technique--use the linked cell.)

jayklmno wrote:

Yes,

myAddr = "Liq_Table"
myPos = "Cells(NextRow + 1, 2)"

When I run it this way, I get a Run-time error '1004': "Method 'Range' of
object'_Worksheet' failed" here...

With wks.Range(myPos)

"Dave Peterson" wrote:

Did you try changing the linked cell so that it points at that range.

myAddr = "A1"
becomes
myAddr = "liq_table"



jayklmno wrote:

Both of these solutions work to create the text box where I want it, but in
order for me to have the box pull in the correct range, in this case a
formatted table,
I need to change the formula linked to the object...

which is normally
=EMBED("Forms.TextBox.1","")

to
=Liq_Table

The problem seems to be in the recorded code, once the object is created and
selected, Selection.Formula = "=Liq_Table" doesn't work.

Any suggestions?

--

Dave Peterson


--

Dave Peterson

That code works, but the only thing that's in the box is the Text from the
frst cell of the range. I am still left with the
=EMBED("Forms.TextBox.1",""). Is there a line of code you wrote that is
changing this formula?

"Dave Peterson" wrote:

myPos expects a string--not a formula.

If you want to use a range:

Option Explicit
Sub Macro2()
Dim OLEObj As OLEObject
Dim myAddr As String
Dim myPos As Range
Dim NextRow as long
Dim wks As Worksheet

myAddr = "A1" 'or whatever range name you want

Set wks = ActiveSheet

With wks
'something here that defines NextRow
Nextrow = 12 'whatever.
set myPos = .Cells(NextRow + 1, 2)
with myPos
Set OLEObj = .Parent.OLEObjects.Add(ClassType:="Forms.TextBox.1 ", _
Link:=False, DisplayAsIcon:=False, _
Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)

OLEObj.LinkedCell = .Parent.Range(myAddr).Address(external:=True)
end with
End With

End Sub

If you're using a textbox from the Drawing toolbar, you can use the formula
technique. But I thought you wrote you were using the textbox from the control
toolbox toolbar (so don't use that technique--use the linked cell.)

jayklmno wrote:

Yes,

myAddr = "Liq_Table"
myPos = "Cells(NextRow + 1, 2)"

When I run it this way, I get a Run-time error '1004': "Method 'Range' of
object'_Worksheet' failed" here...

With wks.Range(myPos)

"Dave Peterson" wrote:

Did you try changing the linked cell so that it points at that range.

myAddr = "A1"
becomes
myAddr = "liq_table"



jayklmno wrote:

Both of these solutions work to create the text box where I want it, but in
order for me to have the box pull in the correct range, in this case a
formatted table,
I need to change the formula linked to the object...

which is normally
=EMBED("Forms.TextBox.1","")

to
=Liq_Table

The problem seems to be in the recorded code, once the object is created and
selected, Selection.Formula = "=Liq_Table" doesn't work.

Any suggestions?

--

Dave Peterson


--

Dave Peterson