Nope. That's how the textbox is embedded into the worksheet. Don't change what
appears in that formulabar.

The linkedcell for that textbox is a single cell--not multiple cells.

Don't do this...
You could concatenate the data with all the cells in a range with a formula in
the linked cell:

=a2&" "&a3&" "&a4&" "&a5
(for example)

But then as soon as you type something into the textbox, you'll see that the
formula in the linked cell is gone.



jayklmno wrote:

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


--

Dave Peterson