In this post you will learn how to use Excel VBA to save textbox . We will be doing this exercise with an input prompt as well as user forms.
Problem : When working with excel vba, we came across a situation to prompt users to enter data and save that data in excel for future use. We get different scenarios to read the input data. This might be a single input prompt or from a user form.
Solution : To read the input data and save it into the excel cell we can use input prompt action or use a userform. Below, I have created an excel file with 2 buttons. On click of button 1, a pop up opens which prompts the user to input some value. On click of the second button, it opens up a user form and prompts for user input.
Table of Contents
How to create the buttons and write vba code in macro?
Before we start, we need to create two buttons for this exercise. Go to the DEVELOPER tab (screenshot 1) and click on insert and select the button control (screenshot 2). A “+” symbol appears on the excel sheet. Drag or draw a symbol, the button will be created with an action to create a new macro (screenshot 3).
Hit OK with the same name or you can change as per your requirement.
Write Excel VBA macro to save textbox value
Once we create the buttons as said above, now it’s time to create the vba macro code to perform our action. You need to open the VBA section. This can be done in different ways.
- Go to the VIEW tab and click on Macros options -> View Macros. This will open a new window where you can see and write the code.
- With control key pressed (ctrl) choose the button to update the macro.Right click the button and click Assign Macro. This will pop up to edit the macro.
Excel VBA save textbox value using InputBox prompt
Using the InputBox method we can prompt user to input a value and save the data in desired cells as below. For this, you need to create a method on button click and then write below code.
Sub Button1_Click()
Dim strInput As String
strInput = InputBox(“Enter a value to save”, “Save text box value”, “#####”)
Cells(1, 1) = strInput
End Sub
Excel VBA save textbox value using UserForm prompt
You can also read the data through a userform application in excel. For this, we need to create a user form first, then design the user interface to read the text and a button to save the data.
To open the user form when we click the button on excel, we need to initiate or show the form. Below code is used to open a form in excel vba
Sub UserForm_Click()
‘opens user form
UserForm1.Show
End Sub
Once the form is open, we can read the input and save the data to desired cells. The code to read and save the textbox value to a cell is as below
Private Sub CommandButton1_Click()
‘save textbox value to required cell
Cells(2, 1) = TextBox1.Text
End Sub
This way you can read the text box value and save it using Excel VBA. You can download the sample below.