Fahim Shahriyar Dipto
A textbox is a portable tool that is used to cover important text or information in Excel. You can drag the text box anywhere in the sheet. Also, it is possible to link a cell to the textbox. But you cannot link multiple cells in a textbox. To do this, you need to join the cells initially with Excel’s built-in function. Then you need to add the link to a textbox. In this article, we are going to show you some simple and straightforward ways to link textbox to multiple cells in Excel. So, let’s get started.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
4 Methods to Link Textbox to Multiple Cells in Excel
To link multiple cells to a textbox in Excel, you need to create a dataset first. Here, we have taken a dataset of the Sales Report of ABC Company. We will link the total sales in a textbox here.
Not to mention, we have used Microsoft 365 version. You may use any other version at your convenience.
1. Applying Ampersand Operator
While linking with multiple cells, you need to insert the Ampersand (&) operator. It joins two or more cells together. Then, you need to link the cells to a textbox. Follow the steps to do that.
📌 Steps:
- Initially, calculate the Total Sales. Then link the two cells in cell B13 with the below formula.
=B12&" "&C12
- Apparently, move to the Insert tab >> Text >> Text Box.
- Eventually, a textbox has been created like the image below.
- Consequently, select the textbox and go to the formula bar to link the desired cells.
- Additionally, insert the below formula.
=$B$13- After that, press ENTER.
Finally, the cells are linked to the text box like in the image below.
Moreover, do some formatting to give a stylish outlook to the textbox like ours.
Read More: How to Keep Formatting in Excel When Referencing Cells (2 Methods)
2. Using CONCATENATE Function
We may join two or more cells with the CONCATENATE function. It joins the cells and then you need to link the multiple joined cells to the textbox. We have demonstrated the steps for better visualization.
📌 Steps:
- Firstly, move to cell B13 and enter the formula.
=CONCATENATE(B12," ",C12)
- Subsequently, insert the textbox like we mentioned in Method 1 and linked it with the below formula.
=$B$13- Sequentially, press ENTER and format the textbox. The final output is like the image below.
Read More: How to Link Multiple Cells in Excel (4 Methods)
3. Employing VBA Macros to Link Textbox to Multiple Cells
You can insert a textbox from the Controls option of the Developer tab. Then, you need to use the VBA Macros to link the textbox to a cell. All you need is just to follow the steps.
📌 Steps:
- Initially, navigate to the Developer tab >> Choose Insert from the Controls option >> pick Text Box under ActiveX Control.
- Draw a text box with the help of the (+) plus sign.
- Eventually, right-click on the sheet name and choose View Code from the Context Menu.
- Consequently, in the Visual Basic editor write up the following code.
Sub link_txtbox()
TextBox1.Text = Range("C12").Value
End SubThe above code will link up the cell value of C12 to the textbox as we entered the command TextBox1.Text = Range(“C12”).Value
Finally, run the code with the F5 key and you will find that the cell is linked to the textbox.
Read More: How to Link Cells in Excel (7 Ways)
4. Incorporating Data Validation Tool
You can use the Data Validation tool for creating a dynamic textbox. That means when you change a value from the drop-down list, the linked text box will change accordingly. Follow the below procedure to use it.
📌 Steps:
- Firstly, select cell B5 and hover over the Data tab >> choose Data Validation from the Data Tools
- At this moment, the Data Validation window pops out. Select List in Allow box and type the Student Name in the Source
- Lastly, hit OK.
- Now, insert a textbox in cell B7 as we mentioned in Method 1. To establish the link of the cell input the formula below.
=$B$5Now the textbox is attached with cell B5.
However, if you changed the value from the dropdown the textbox will change accordingly. See the below GIF.
Read More: How to Link Cells in Same Excel Worksheet (4 Quick Ways)
How to Convert TextBox to Cell in Excel
We can also change the textbox to cell content through the VBA code. It is pretty simple to use the code. But firstly, you need to insert textboxes to the cell B5:B11 range.
- Eventually, navigate to the Developer tab >> choose Visual Basic.
- Subsequently, from the Visual Basic editor select the Insert tab >> Module>>Module 1.
- Sequentially, write up the following code.
Sub ConvertTextBoxToCell()
Dim Sh_xRg As Range
Dim Sh_xRow As Long
Dim Sh_xCol As Long
Dim Sh_xTxtBox As TextBox
Set Sh_xRg = Application.InputBox("Select a cell):", "Convert Text Box to Cell ", _ ActiveWindow.RangeSelection.AddressLocal, , , , , 8)
Sh_xRow = Sh_xRg.Row
Sh_xCol = Sh_xRg.Column
For Each Sh_xTxtBox In ActiveSheet.TextBoxes
Cells(Sh_xRow, Sh_xCol).Value = Sh_xTxtBox.Text
Sh_xRow = Sh_xRow + 1
Next
End Sub
- Moreover, run the code with the F5 key and you will find the InputBox for selecting a cell where to start. In our case, it is cell $D$5.
- Lastly, hit OK.
As you can see, the entity in the textboxes has been converted into the cell value.
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
Conclusion
That’s all about today’s session. And these are some easy methods to link textbox to multiple cells in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Visit our website ExcelDemy, a one-stop Excel solution provider, to find out about diverse kinds of excel methods. Thanks for your patience in reading this article.
