How to concatenate texts of multiple cells into a textbox in Excel?
It may be easy for you to concatenate texts of multiple cells into a new cell with formula. But do you know how to concatenate multiple cell values into a textbox (ActiveX Control)? Actually, the method in this article can help you to achieve it.
Recommended Excel Productivity Tools
The below method can help you to concatenate texts of multiple cells into a textbox in Excel.
1. In the worksheet you want to concatenate multiple cells, insert a textbox by clicking Developer > Insert > Text Box (ActiveX Control). See screenshot:
2. Then click Developer > Insert > Command Button (ActiveX Control) to insert a command button into the worksheet.
3. Right-click the inserted command button, then select View Code from the context menu. See screenshot:
4. In the Microsoft Visual Basic for Applications window, please replace the original code with below VBA code.
VBA code: Concatenate texts of multiple cells into a textbox
Private Sub CommandButton1_Click() Dim xRg As Range Dim xCell As Range Dim xStr As String On Error Resume Next Set xRg = Application.Selection If xRg Is Nothing Then Exit Sub With Me.TextBox1 .Text = vbNullString .MultiLine = True .WordWrap = True For Each xCell In xRg xStr = xCell.Value .Text = Me.TextBox1 & xStr & Chr(10) Next End With End Sub
Note: CommandButton1 is the name of the inserted command button. Please change it based on your need.
5. Turn off the Design Mode by clicking Developer > Design Mode.
6. Select the cells you need to concatenate the texts into a textbox, then click the Command Button. And then all selected cells’ contents are concatenated into the textbox immediately as below screenshot shown.
- How to auto select text of a textbox when it is selected in Excel?
- How to autocomplete a textbox when typing in Excel?
- How to clear contents of textbox when clicked in Excel?
- How to disable editing in textbox to prevent user inputting in Excel?
- How to format a textbox as a percentage in Excel?
Recommended Productivity Tools for Excel
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 months agohola Como el boton puede tomarme las celdas visibles, ya que a veces oculto celdas y me las toma igual pero no quiero
To post as a guest, your comment is unpublished.· 3 months agoMuy bueno, Y si le quiero poner ; para separar los textos ¿cómo lo hago?