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.
- 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.
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
- 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 Text with Spaces to Number in Excel (4 Ways)
Similar Readings
- How to Convert Date to Week Number of Month in Excel (5 Ways)
- Convert 3 Letter Month to Number in Excel (8 Suitable Methods)
- How to Fix All Number Stored as Text in Excel (6 Easy Solutions)
- Convert Percentage to Whole Number in Excel (4 Methods)
- How to Convert Time to Number in Excel (5 Easy Methods)
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 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
- How to Convert Scientific Notation to Number in Excel (7 Methods)
- Fix Convert to Number Error in Excel (6 Methods)
- How to Convert Hours and Minutes to Decimal in Excel (2 Cases)
- Convert Degrees Minutes Seconds to Decimal Degrees in Excel
- How to Convert Percentage to Decimal in Excel (7 Methods)
- Convert Degrees Decimal Minutes to Decimal Degrees in Excel