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.
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.
- 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.
Read More:Â How to Hide Textbox Using Excel VBA
Excel VBA to Convert Textbox Value to Number: 2 Examples
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.
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.
- Secondly, right-click on the textbox to open the Context Menu.
- Select View Code from there.
- After selecting View Code, you will see a Code window like the picture below.
- In the Code window, type the below code:
Private Sub TextBox1_Change()
TextBox1.Value = CInt(Range("B5"))
End Sub
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.
- In the following step, click inside the Textbox and press any key to see the result.
- Now, change the value of Cell B5 to 33.33.
- Put the cursor inside the Textbox.
- Finally, press any key to see the updated result.
Read More: How to Convert TextBox Value to Cell with VBA in Excel
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.
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.
- In the second step, right-click 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 Format TextBox for Phone Number in Excel VBA
Download Practice Book
You can download the practice book from here.
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. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.
