In this tutorial, I am going to show you the step-by-step procedures on how to convert a text box to a cell in Excel. You can use these steps for any type of dataset to quickly convert text boxes to cell values. Throughout this tutorial, you will also learn some important Excel tools and techniques which will be very useful in any excel related task.
Download Practice Workbook
You can download the practice workbook from here.
Step-by-Step Procedures to Convert Text Box to Cell in Excel
We have taken a concise dataset to explain the steps clearly. The dataset has approximately 6 rows and 3 columns. Initially, we are keeping all the cells in General format. For all the datasets, we have 3 unique columns which are Employee Name, Location (TextBox), and Location (Cell). Although we may vary the number of columns later on if that is needed.
Step-1 Creating Base Dataset
In this first step, we will create the starting Excel dataset for our operation. Follow the steps below to do this.
- First, insert the following columns with the sample employee names.
- Then, insert some text boxes and enter the locations as in the image below.
Read More: How to Insert Text Box in Excel (5 Easy Methods)
Step-2 Inserting VBA Code
Now, we have to insert the necessary VBA code that will convert the text box to a cell. Let us see how to do this.
- For this method, go to the Developer tab and select Visual Basic.
- Now, select Insert in the VBA window and click on Module.
- Next, type in the formula below in the new window:
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
Here, we are creating an input box to take user input using the Application.InputBox method. Next, ActiveWindow.RangeSelection method represents cells that the users select.
Read More: How to Insert an Embedded Text Box in Excel (4 Easy Ways)
Step-3 Running VBA Macro
Once we have inserted the VBA code inside the module, now we can run this code directly from the Macros option. Here are the steps to achieve this.
- To begin with, click on the Developer tab again and select Macros.
- Then, select the macro name and click on Run.
- Consequently, this will show a popup window.
- Here, click on the cell in which you want to enter the data and click OK.
- As a result, this will convert the text box values into cell values as we wanted.
Read More: How to Get a Text Box Linked to Cell in Excel (4 Examples)
How to Convert Cell to Text Box in Excel
If you want to perform the reverse operation of what we did previously, then you can follow this method to do that quickly to convert a cell to a text box in excel.
- To start with, click on the icon on the top left corner of the worksheet as in the image below.
- Next, click on the Fill Color drop-down above.
- Then, select any color as the Fill Color.
- Now, click on any of the cells to convert it to a text box.
- After that, press Ctrl+1 to open Format Cells.
- Then, go to the Fill tab and select the color, White.
- Next, go to the Border tab and set the top and left border as the color black.
- Similarly, set the bottom and right border as the color white.
- Now, click on OK.
- Finally, this should format the cell as a text box.
Read More: Formatting Text Box in Excel: All Properties to Explore
How to Create Dynamic Text Box in Excel
In this final method, we will see a very important feature that excel has which allows us to create dynamic text boxes instead of hard-coding the data into them.
- Firstly, select cell B5 and go to the Data tab at the top of the screen.
- Now, click on Data Validation under Data Tools.
- Next, in the Data Validation window, set List as the Allow option from the drop-down.
- Then, type the employee names in the Source field as in the image below and click OK.
- Immediately, this will insert a drop-down list inside cell B5.
- Now, click on cell B7 and go to the Insert tab thereafter.
- Then, click on Text Box under Text.
- After that, type in the following formula inside the text box:
- Then, press Enter.
- Finally, this will create a dynamic text box.
I hope that you were able to apply the methods that I showed in this tutorial on how to convert a text box to a cell in Excel. As you can see, it involves quite a few steps to achieve this. So carefully follow these while applying them to your own dataset. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more Excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.