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.
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?
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.· 1 years 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.· 1 years agoMuy bueno, Y si le quiero poner ; para separar los textos ¿cómo lo hago?