Introduction: 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:

1.Label

2.TextBox

3.ComboBox

4.ListBox

5.CheckBox

6.OptionButton

7.ToggleButton

8.Frame

9.CommandButton

10.TabStrip

11.MultiPage

12.ScrollBar

13.SpinButton

14.Image

15.Select Objects

16RefEdit

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()

'Addition operator

Ans.Text = Val(Op1.Text) + Val(Op2.Text)

End Sub

Private Sub CommandButton30_Click()

'Plus or minus operator

Ans.Text = -Ans.Text

End Sub

Private Sub CommandButton31_Click()

If (Val(Op2.Text) = 0) Then

'Division operator

Ans.Text = "Division by Zero"

Exit Sub

Else

Ans.Text = Val(Op1.Text) / Val(Op2.Text)

End If

End Sub

Private Sub

CommandButton32_Click()

'Multiplication operator

Ans.Text = Val(Op1.Text) * Val(Op2.Text)

End Sub

Private Sub CommandButton33_Click()

'Minus operator

Ans.Text = Val(Op1.Text) - Val(Op2.Text)

End Sub

Private Sub CommandButton35_Click()

'Square root operator

Ans.Text = Ans.Text ^ (1 / 2)

End Sub

Private Sub CommandButton37_Click()

'1/x operator

If (Val(Ans.Text) = 0) Then

Ans.Text = "Division by Zero"

Exit Sub

Else

Ans.Text = 1 / (Ans.Text)

End If

End Sub

Private Sub CommandButton38_Click()

Ans.Text = 0

End Sub

Private Sub UserForm_Click()

End Sub

In order to automatically open the UserForm when excell is opened type the following code in the ThisWorkbook


Private Sub Workbook_Open()

UserForm1.Show

End Sub

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()

Family_Form.Show

End Sub

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()

'Empty NameTextBox

NameTextBox.Value = ""

'Empty PhoneTextBox

PhoneTextBox.Value = ""

'Empty CityListBox CityListBox.Clear

'Fill CityListBox

With CityListBox

.AddItem "Kochi"

.AddItem "Mumbai"

.AddItem "Delhi"

.AddItem "Agartala"

.AddItem "Agra"

.AddItem "Agumbe"

.AddItem "Ahmedabad"

.AddItem "Aizawl"

' you can add your other cities here

End With

'Empty StatusComboBox

StatusComboBox.Clear

'Fill StatusComboBox

With StatusComboBox

.AddItem "Lower Class"

.AddItem "Middle Class"

.AddItem "Upper Class"

End With

'Uncheck DataCheckBoxes

'Set no car as default

CarOptionButton2.Value = True

'Empty Members

Members.Value = ""

'Set Focus on Name

TextBox NameTextBox.SetFocus

End Sub

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:

Private Sub
MemberButton_Change()

Members.Text = MemberButton.Value

End Sub

Private Sub OKButton_Click()

Dim emptyRow As Long

'Make Sheet1 active Sheet1.Activate

'Determine emptyRow

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information

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"

Else

Cells(emptyRow, 6).Value = "No"

End If

Cells(emptyRow, 7).Value = Members.Value

End Sub


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()

Call UserForm_Initialize

End Sub


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()

Unload Me

End Sub


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.

Comments

author
yeyaaaaa (author)2017-05-20

Can you make a GUI with Libre Office base as well?

author
EricO42 (author)2016-04-22

Thank you so much for this instructable - it is exactly what I need. I have a question for you - is it possible to do the reverse - also in the same form? For instance, I track telecom tickets which come into our ticketing system and enter them into excel. I would like to be able to VLOOKUP (or equivalent) in the form you created and I modified and then have it populate the fields based on the corresponding data in Excel. This would allow me to switch the "Status" field from 'Open' to 'Resolved'. I would like to then be able to then re-submit/save the newly edited data back into the spreadsheet.

Does this make sense, and is it possible without VBA?

I am basically trying to avoid using Microsoft Access! :)

Thank you again

author
calko (author)2015-09-21

Super instructable

author
NIKHILP3 (author)2015-09-18

I have also included Form to automate the database (Automate Survey) in excel sheet from STEP 6 onward.

author
NIKHILP3 (author)2015-09-18

Thank-you all for your comments and Do supporting me

author
RichardL45 (author)2015-09-17

Sorry , that is German. I little weak in English. My question is there is any way to include vedio in excel form .

author
NIKHILP3 (author)RichardL452015-09-17

Its good to hear from you! We cann't directly include video like picture , which i have included in my Form. But you can play video by adding a link to the form

First create a button and double click on the button , then add the following code.

Dim WMP as
Object
Set WMP =
CreateObject("new:{6BF52A52-394A-11d3-B153-00C04F79FAA6}")
Wmp.OpenPlayer "C:\Users\Public\Videos\Sample
Videos\Wildlife.wmv"

First create a button and double click on the button .

If you want to play Youtube movies in your Form just add the following code for example:
PrivateSub CommandButton1_Click()
WithMe.ShockwaveFlash1.LoadMovie0,"http://www.youtube.com/v/jmHkMsrycBw"
EndWith
EndSub

author

Good Work!
Do continue to add new Instructables!

author
NIKHILP3 (author)NIKHILP32015-09-17

There is problem in the previous code: Just all the code cannot be see.

I have corrected it.

PrivateSub CommandButton1_Click()

WithMe.ShockwaveFlash1

.LoadMovie 0,"http://www.youtube.com/v/jmHkMsrycBw"

EndWith

EndSub

You can include other vedio by changing the link "http://www.youtube.com/v/jmHkMsrycBw"

author
VinayakT1 (author)NIKHILP32015-09-17

I have tried the code. It works. Thank you sir.

author
AnushkaShetty (author)2015-09-18

Super work!

author
TintuLukas (author)2015-09-18

I thought it was complicated, your instructables made it simple. Thanks

author
arunvkyn (author)2015-09-18

Great work

author
vishuvft (author)2015-09-18

The form is very beautiful. Good Job

author
SharookK (author)2015-09-18

Nice presentation

author
SharoneSKumar made it! (author)2015-09-18

Is there ant way to automatically open the form?

1.PNG
author
ImranKhanD (author)SharoneSKumar2015-09-18

@Sharone,

NIKHIL sir already added that in the step 4. See it. :)

author
ImranKhanD (author)SharoneSKumar2015-09-18

Great instructable

author
LusiJhon (author)2015-09-17

Nice Job

author
TominTS (author)2015-09-17

Good work

author
LinsonTZ (author)2015-09-17

I got inspired by your instructable .This is the first time I have tried to make a form in excel.

author
LinsonTZ (author)2015-09-17

Great idea!

author
VinayakT1 (author)2015-09-17

The new Form you have updated is really Great !

**********************************

Its Awesome!

**********************************

It will really a help in my survey project .


author
NIKHILP3 (author)VinayakT12015-09-17

Thank you!

author
VinayakT1 (author)2015-09-17

When I run the code after opening the saved excel file , there is a message showing macros are disabled. What should I do. How to enable macro?

author
NIKHILP3 (author)VinayakT12015-09-17
First Click the Microsoft Office Button , and then click Excel Options.

In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.
    1. Note The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. On the Developer tab, in the Code group, click Macro Security.

  2. Under Developer Macro Settings, select the Trust access to the VBA project object model check box.

Then save and run it again it will work

author
VinayakT1 (author)NIKHILP32015-09-17

Thank you sir.Thanks for your replay. I have done it.

author
NIKHILP3 (author)VinayakT12015-09-17

"My pleasure" . Keep asking questions , if you have any confusions.

author
RichardL45 (author)2015-09-17

Gute Arbeit. :)

Ich will wissen , kann ich schließen vedio darin.

author
NIKHILP3 (author)RichardL452015-09-17

Gute Arbeit mean 'Great work' no?

author
NIKHILP3 (author)RichardL452015-09-17

I don't understand your language!

author
NIKHILP3 (author)2015-09-02

Please comment if you have any suggestions

author
sunnyarun321 (author)NIKHILP32015-09-02

Super Instructables. I like the method you have presented

author
NIKHILP3 (author)sunnyarun3212015-09-16

Thanks.

author
VasuttanT (author)2015-09-15

Great job!

author
NIKHILP3 (author)VasuttanT2015-09-16

Thank you Vasutta...

author
MuhamadT1 made it! (author)2015-09-15

Great inspiration to make bigger form.Thanks.

1.PNG
author
NIKHILP3 (author)MuhamadT12015-09-15

Thanks. Its so kind of you , and do making more Forms like this.

author
123nikhi made it! (author)2015-09-15

Easier than Visual studio :)

2.PNG
author
NIKHILP3 (author)123nikhi2015-09-15

Ya it is easier than Visual studio.But it does not have as much tools as Visual studio. I am glad you made it.

author
SruthiD made it! (author)2015-09-15

Simple and easy to understand !

1.PNG
author
NIKHILP3 (author)SruthiD2015-09-15

Thank you.

author
Samiran (author)2015-09-02

An old but easy and effective way to make gui.

author
NIKHILP3 (author)Samiran2015-09-02

If you don't have any other software available you can make use of Excel and make your own GUI. First try to make simple GUI and you will find it interesting , and you can make many other GUIs