Excel VBA to Convert Textbox Value to Number (2 Ideal Examples)

In this article, we will learn to convert the textbox value to a number with VBA in Excel. A textbox is a rectangular box that displays different texts or numbers on an excel sheet. You can view or add comments in a textbox in Excel. Today, we will show 2 ideal examples. Using these examples, you can convert the textbox value to a number easily. So, without any delay, let’s start the discussion.


Download Practice Book

You can download the practice book from here.


How to Add Textbox in Excel?

Before performing any textbox operation, you need to insert a textbox in Excel. The process of adding a textbox in Excel is different but simple. You can add it from the Developer tab. Let’s follow the steps below to see how we can add a textbox in Excel.

STEPS:

  • Firstly, go to the Developer tab and select the Insert icon. A drop-down menu will appear.
  • After that, select the Text Box icon from the ActiveX Controls section.

How to Add Textbox in Excel?

  • Then, put the cursor on the sheet. It will turn into a small black plus sign.
  • Finally, you need to draw a textbox on the sheet.

2 Examples of Excel VBA to Convert Textbox Value to Number

1. Excel VBA to Convert Textbox Value to Number Using Cell

In the first example, we will convert a textbox value to a number using a cell. Here, we will store a decimal number as a text value in a cell. The textbox will convert that decimal number into an integer number and store it. In the picture below, you can see Cell B5 contains 22.75 as a text value and a Textbox right beside it. We will try to store the number in the textbox.

Excel VBA to Convert Textbox Value to Number Using Cell

Let’s follow the steps below to learn more about the method.

STEPS:

  • In the first place, go to the Developer tab and select Design Mode.

Excel VBA to Convert Textbox Value to Number Using Cell

  • Secondly, rightclick on the textbox to open the Context Menu.
  • Select View Code from there.

Excel VBA to Convert Textbox Value to Number Using Cell

  • After selecting View Code, you will see a Code window like the picture below.

Excel VBA to Convert Textbox Value to Number Using Cell

  • In the Code window, type the below code:
Private Sub TextBox1_Change()
TextBox1.Value = CInt(Range("B5"))
End Sub

Excel VBA to Convert Textbox Value to Number Using Cell

Here, we have inserted a single line of code inside default commands. This code will convert the text value into an integer number. If you want to convert the value into double or long format, then type CDbl or CLng in place of CInt.

  • Press Ctrl + S to save the code.
  • Then, close the Visual Basic window.
  • After that, go to the Developer tab and deselect Design Mode.

Excel VBA to Convert Textbox Value to Number Using Cell

  • In the following step, click inside the Textbox and press any key to see the result.

Excel VBA to Convert Textbox Value to Number Using Cell

  • Now, change the value of Cell B5 to 33.33.
  • Put the cursor inside the Textbox.

Excel VBA to Convert Textbox Value to Number Using Cell

  • Finally, press any key to see the updated result.

Excel VBA to Convert Textbox Value to Number Using Cell

Read More: How to Convert Text with Spaces to Number in Excel (4 Ways)


Similar Readings


2. Add Textbox to Convert Value to Number with Excel VBA

In the second example, we will enter two values in two textboxes and show their summation in the third textbox. Here, we will add text boxes to convert values to numbers with Excel VBA.

Add Textbox to Convert Value to Number with Excel VBA

Let’s observe the steps below to see how we can convert textbox values to numbers.

STEPS:

  • Firstly, go to the Developer tab and select Design Mode.

Add Textbox to Convert Value to Number with Excel VBA

  • In the second step, rightclick on the first textbox.
  • Select View Code from the Context Menu. It will open the Code window.

  • In the Code window, you will see the default commands for the 3 text boxes.

  • After that, paste the code below inside each section of the text boxes like the picture below:
If IsNumeric(TextBox1) And IsNumeric(TextBox2) Then
TextBox3 = CStr(CLng(TextBox1) + CLng(TextBox2))
End If

This code will check if TextBox1 and TextBox2 contain numbers. If they contain numbers, then it will add them and store them in TextBox3. You must paste the same code for each textbox.

  • Press Ctrl + S to save the code.
  • Now, close the Visual Basic window.
  • At this moment, navigate to the Developer tab and deselect Design Mode.

  • In the following step, type 23 in Textbox 1 and 12 in Textbox 2.
  • Then, click inside Textbox 3 and press any key to see the result.

  • Finally, you can change the values of Textbox 1 & 2 to see the change in Textbox 3.

Read More: How to Convert Exponential Value to Exact Number in Excel (7 Methods)


Conclusion

In this article, we have demonstrated 2 examples of Excel VBA to Convert Textbox Value to Number. I hope this article will help you to perform your tasks easily. Moreover, we have also discussed the method to add a textbox in Excel. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. You can also visit the ExcelDemy website for more articles like this. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles

Mursalin Ibne Salehin

Mursalin Ibne Salehin

Hi there! This is Mursalin. I am an Excel and VBA content developer at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo