2

Hi I am new to VBA but want to do something seemingly simple, however I cannot figure it out.

I would like to link multiple cells to a textbox and have the values be on different lines. Ex. A1 = Blue; B1 = Red; C1 = Yellow

I want to have a textbox return:
Red
Blue
Yellow

I cannot find an answer to this anywhere.

The code I have now is:

Sub Macro2()

' Macro2 Macro

    ActiveSheet.Shapes.Range(Array("TextBox 2")).Select
    Selection.Formula = "=$A$1"

End Sub

This returns the value of A1 into the text box, but I also need the value of B1 and C1.

Any help would be appreciated. Thanks

Update:

Elbert Villarreal's answer solved my problem, but I have one last question.

I have multiple rows that I would like to do this for (A2, A3, A4). Is there a way to do a loop for these different rows of data?

Stobi1
  • 21
  • 1
  • 4
  • Do you want the values from B1 and C1 in the same textbox or new ones? – Marco Sep 26 '16 at 16:18
  • 1
    Once you get this working with `.Selection`, make sure you know why it's working. Then, remove the `.Select`, as it's best to [avoid using `.Activate`/`.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). – BruceWayne Sep 26 '16 at 16:28
  • I have multiple rows that I would like to do this for (A2, A3, A4). Is there a way to do a loop for these different rows of data? – Stobi1 Sep 26 '16 at 20:32

1 Answers1

2

With this you can do what you ask:

Sub Stobi1()
        Dim txt As String 'Where to store the text/data
        Dim myTxtBox As Shape 'where to store the textbox that is a Shape

        Set myTxtBox = ActiveSheet.Shapes("myTextBox") 'take the name of your textbox
        txt = Range("A1").Value & Chr(10) & Range("B1").Value & Chr(10) & Range("C1").Value 'this whay you can take the strings of the cells and "write it" with a new line between

        myTxtBox.TextFrame2.TextRange.Characters.text = txt 'Put your text inside the TextBox
End Sub
Elbert Villarreal
  • 1,696
  • 1
  • 11
  • 22
  • Thanks for the help! This works perfectly. One last question. I have multiple rows that i would like to do this for (A2, A3, A4). Is there a way to do a loop for these different rows of data? – Stobi1 Sep 26 '16 at 16:45
  • For sure, tomorrow I will ask you for some info... Here is 12:am... But tell me, you want to go over A1, A2,A3 and B1 and C1??? – Elbert Villarreal Sep 27 '16 at 06:23
  • It is multiple columns of information, to be precise it's 6 columns. So for the first part of the text box I would like the all information from row 1, then below that all the information from row 2, then row 3 ect. – Stobi1 Sep 27 '16 at 12:47