Concatenate cells into Text Box, Excel

• Oct 4, 2002

• #2

zliazmei

Structural

Feb 5, 2002

97

Ran

May be my uderstanding of your question is not correct, but if it is try this:

Let say:
in A1 you typed b=
in B1 you have 171.234
In C1 you have 200.2356

the string =A1&Fixed(B1,1,1)&"+"&FIXED(C1,3,1)&"
will give in your cell the result:
b=171.2+200.236 as a text

Hope it helps

Zmei

 

Upvote 0 Downvote

• Oct 4, 2002

• Thread starter
• #3

ran13

Aerospace

Dec 14, 2001

11

You're right; your example will give you that result in a cell. However, I want the result to be in a text box, not another cell.

 

Upvote 0 Downvote

• Oct 4, 2002

• #4

yakpol

Structural

Jun 1, 2001

450

ran13,
To automate textbox you have to use VBA, there's no a way around it. The following function will update the textbox every time you change the cell values in the worksheet. Place it in the worksheet vba module:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = ActiveCell
    ActiveSheet.Shapes("Text Box 1").Select
    Selection.Characters.Text = Range("B6") & Range("C6")
    rng.Select
End Sub

Hope it helps!

 

Upvote 0 Downvote

• Oct 4, 2002

• Thread starter
• #5

ran13

Aerospace

Dec 14, 2001

11

I appreciate your help.

Thanks,

Roger

 

Upvote 0 Downvote

• Oct 4, 2002

• #6

ivymike

Mechanical

Nov 9, 2000

5,653

first do what zmei suggested:
in A1 you typed b=
in B1 you have 171.234
In C1 you have 200.2356
the string =A1&Fixed(B1,1,1)&"+"&FIXED(C1,3,1)&"
will give in your cell the result:
b=171.2+200.236 as a text



then try this:

create the textbox
select the textbox by clicking on it once
in the equation bar, type your equation (such as =A1&Fixed(B1,1,1)&"+"&FIXED(C1,3,1)&"

lemme know if that works. It works for me in xl2k.

 

Upvote 0 Downvote

• Oct 4, 2002

• #7

ivymike

Mechanical

Nov 9, 2000

5,653

nevermind, it only seems to work using a single cell of text (so you'd have to combine your strings elsewhere first).

 

Upvote 0 Downvote

• Oct 4, 2002

• #8

pmover

Mechanical

Sep 7, 2001

1,507

ran13,
ivymike response will work, with another technique.
1) concatenate all the input data into another cell
2) create textbox
3) with textbox selected (not interior of textbox, but textbox border), press the "=" and then the cell with all the concatenated cells.
pmover

 

Upvote 0 Downvote