Intro: Make Your Own GUI(graphical User Interface) Without Visual Studio in Microsoft Excel
It is easy to create your own user form in Microsoft Excel.
Lets begin to create a simple calculator.
I have also included Form to automate the database (Automate Survey) in excel sheet from STEP 6 onward.
Step 1: CREATING FORM.
Just open Microsoft Excel.
Press"Alt + F11 " that will open a Visual Basic window.
Select UserForm from Insert menu. That will open a User form window like above
Step 2: Controls and the Toolbox
The Controls for a UserForm can be found on what is known as the "Toolbox". When we Insert a UserForm from within the VBE (Visual Basic Environment) via Insert > UserForm the Toolbox will be displayed by default. Or we can go to View>Toolbox. The Toolbox contains a single page tab aptly called "Controls". It is here that we will see all the Visual Basic controls plus any ActiveX Controls that may have been added. By default there will be sixteen Controls available these are:
Step 3: Drag and Place Tools to Useform
In the VBAProject under the form a form with default name UserForm1 will appear select that User form .
Then a user form window appear.
The size of the window can be adjusted.
Drag and drop the Command Button in the useform.Use command Button for each operators.
This can be done in a simple way by just copy a button by Ctrl+C and pasting by Ctrl+V.
Then change the size of button to make then somewhat look like a calculator.
Then change the Caption of each button from the properties.
Just right click on button >Properties > Caption .Change the caption of each button.
Also add TextBox tool to userform ,which can be used to type the operands
We can add a Background image by selecting an image file usually jpeg files from a file.
i.e Select Userform then right click on the form >Properties> image then select a picture
Step 4: Type the Code for Each Tools
Select each CommandButton . and type the code for each buttons .
My code is given below , please note that the Command Button caption will be different.
Please see that caption for each command button. Double click on the commandButton and type code for each of the commands
Private Sub CommandButton14_Click()
Ans.Text = Val(Op1.Text) + Val(Op2.Text)
Private Sub CommandButton30_Click()
'Plus or minus operator
Ans.Text = -Ans.Text
Private Sub CommandButton31_Click()
If (Val(Op2.Text) = 0) Then
Ans.Text = "Division by Zero"
Ans.Text = Val(Op1.Text) / Val(Op2.Text)
Ans.Text = Val(Op1.Text) * Val(Op2.Text)
Private Sub CommandButton33_Click()
Ans.Text = Val(Op1.Text) - Val(Op2.Text)
Private Sub CommandButton35_Click()
'Square root operator
Ans.Text = Ans.Text ^ (1 / 2)
Private Sub CommandButton37_Click()
If (Val(Ans.Text) = 0) Then
Ans.Text = "Division by Zero"
Ans.Text = 1 / (Ans.Text)
Private Sub CommandButton38_Click()
Ans.Text = 0
Private Sub UserForm_Click()
In order to automatically open the UserForm when excell is opened type the following code in the ThisWorkbook
Private Sub Workbook_Open()
Step 5: Make Your Own Calculator
You can add many features to this and make your own calculator.
We can do it in Visual studio in same way and can create .exe files.
Thanks and please please vote for this if you like.
Step 6: Make Your Own User Form to Automatically Update the Data in Excel
You can create your own Form to enter Family data , that automatically update your excel sheet by following steps.
Step 7: Add the Controls
To add the controls to the User-form, execute the following steps.
1. Open the Visual Basic Editor. If the Project Explorer is not visible, click View, Project Explorer.
2. Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox. Your screen should be set up as below.
Userform Screen Setup in Excel VBA
3. Add the controls list as in the picture . Once this has been completed, the result should be consistent with the picture of the Userform shown earlier. For example, create a text box control by clicking on TextBox from the Toolbox. Next, you can drag a text box on the Userform. When you arrive at the Car frame, remember to draw this frame first before you place the two option buttons in it.
4. Change the names and captions of the controls according to the table below. Names are used in the Excel VBA code. Captions are those that appear on your screen. It is good practice to change the names of controls. This will make your code easier to read. To change the names and captions of the controls, click View, Properties Window and click on each control.
Step 8: Show the Userform
To show the Userform, place a command button on your worksheet and add the following code line:
Private Sub CommandButton1_Click()
We are now going to create the Sub UserForm_Initialize. When you use the Show method for the Userform, this sub will automatically be executed.
1. Open the Visual Basic Editor.
2. In the Project Explorer, right click on Family_Form and then click View Code.
3. Choose Userform from the left drop-down list. Choose Initialize from the right drop-down list.
4. Add the following code lines:
Private Sub UserForm_Initialize()
NameTextBox.Value = ""
PhoneTextBox.Value = ""
'Empty CityListBox CityListBox.Clear
' you can add your other cities here
.AddItem "Lower Class"
.AddItem "Middle Class"
.AddItem "Upper Class"
'Set no car as default
CarOptionButton2.Value = True
Members.Value = ""
'Set Focus on Name
Step 9: Assign the Macros
We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we click the command buttons on the Userform.
1. Open the Visual Basic Editor.
2. In the Project Explorer, double click on Family_Form.
3. Double click on the Member button.
4. Add the following code line:
Private Sub MemberButton_Change()
MoneyTextBox.Text = MoneySpinButton.Value
End Sub Explanation: this code line updates the text box when you use the spin button.
5. Double click on the OK button.
6. Add the following code lines:
Members.Text = MemberButton.Value
Private Sub OKButton_Click()
Dim emptyRow As Long
'Make Sheet1 active Sheet1.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
Cells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = PhoneTextBox.Value
Cells(emptyRow, 3).Value = CityListBox.Value
Cells(emptyRow, 4).Value = StatusComboBox.Value
If CarOptionButton1.Value = True Then
Cells(emptyRow, 6).Value = "Yes"
Cells(emptyRow, 6).Value = "No"
Cells(emptyRow, 7).Value = Members.Value
Explanation: first, we activate Sheet1. Next, we determine emptyRow. The variable emptyRow is the first empty row and increases every time a record is added. Finally, we transfer the information from the Userform to the specific columns of emptyRow.
7. Double click on the Clear button.
8. Add the following code line:
Private Sub ClearButton_Click()
Explanation: this code line calls the Sub UserForm_Initialize when you click on the Clear button.
9. Double click on the Cancel Button.
10. Add the following code line:
Private Sub CancelButton_Click()
Explanation: this code line closes the Userform when you click on the Cancel button.
Step 10: Test the Userform
Exit the Visual Basic Editor, enter the labels shown in the picture into row 1 and test the Userform.