How to Convert a Text Box into a Cell in Excel – 3 Steps

The dataset showcases Employee Name, Location (TextBox), and Location (Cell).

Step 1 – Creating a Dataset

• Insert a column with the Employee Names.
• Insert text boxes and enter the locations as shown below.

Read More: How to Insert Scrolling Text Box in Excel

Step 2 – Using a VBA Code

Use a VBA code to convert the text box into a cell.

• Go to the Developer tab and select Visual Basic.

• Select Insert in the VBA window and click Module.

• Enter the formula below:

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

An input box is created to take user input using the Application.InputBox method.  ActiveWindow.RangeSelection represents selected cells.

Read More: How to Add a Dynamic Text Box in Excel

Step 3 – Running a VBA Macro

• Click the Developer tab again and select Macros.

• Select the macro name and click Run.

• In the new window, click the cell in which you want to enter data and click OK.

• Text box values are converted into cell values.

How to Convert a Cell into a Text Box in Excel

Steps:

• Click the icon at the top left corner of the worksheet, as shown below.
• Select Fill Color.

• Choose a color.

• Click any cell to convert it into a text box.

• Press Ctrl+1 to open Format Cells.
• Go to Fill and select White.

• Go to the Border tab and set the top and left border to black.
• Set the bottom and right border to white.
• Click OK.

• This is the output.

Read More: How to Anchor Text Box in Excel

How to Create a Dynamic Text Box in Excel

Steps:

• Select B5 and go to the Data tab.
• Click Data Validation in Data Tools.

• Choose List in Allow.
• Enter the employee names in Source.
• Click OK.

• A drop-down list is created in B5.

• Click B7 and go to the Insert tab.
• Click Text Box in Text.

• Use the following formula in the text box:

• Press Enter.

• A dynamic text box is created.

Download Practice Workbook

Download the practice workbook.

Related Articles

• How to Put Space in Text Box in Excel
• How to Create Floating Text Box in Excel

<< Go Back to TextBox | Learn Excel