Learn

How to create userforms in Excel using VBA

A. Create a UserForm

1. Open a new Excel spreadsheet and Save as UserForm.xls

2. Open Visual Basic Editor by right clicking on Worksheet tab

3. Create a UserForm by right clicking on VBAProject

4. Create two text boxes and two command buttons on the UserForm

5. Change the labels on the command buttoons to 'Enter' and 'Clear' through the properties panel

6. Paste the VBA code into the UserForm code (Right click on the small UserForm1 icon on the left and View code)

  

VBA Code for UserForm1

 

'One way code

Private Sub CommandButton1_Click()
Dim Number As Single, Answer As Single, Pi As Single
Pi = 4 * Atn(1) ' Calculates value of Pi

'Input
If Not IsNumeric(TextBox1.Value) Then End  'If the input data is not numberic then end
Number = TextBox1.Value 'Stores the value of the TextBox1 into the variable number.

'Process examples
'Answer = Number / 10
'Answer = Number * 10
'Answer = 2 * Pi * Number
 Answer = 9 / 5 * Number + 32 ' Converts Celsius to Farenheit

 

'Output
TextBox2.Value = Answer
End Sub

 

Private Sub CommandButton2_Click()
TextBox1.Value = ""
TextBox2.Value = ""
End Sub

 

B. Create a Buttom to open the form

1. Select a WorkSheet. Go to the top toolbar and make sure the View => Toolbars => Visual Basic box is ticked.

Make a command button on a worksheet ussing the Control Toolbox

 

2. Double click on ther Command button in Design mode and insert this VBA code into the worksheet

 3. Exit design mode and click on the Command button on your worksheet

 

VBA Code for Worksheet 

Private Sub CommandButton1_Click()
 UserForm1.Show
End Sub

 

C. Modify the UserForm.

1.  Add the labels 'Celsius' and 'Farenheit' above each text box on the form

2. Change the text colors, fonts and form background.

 

To make the calculator work both ways replace the UserForm1 VBA code with

 'Two way code for Temperature Converter

Private Sub CommandButton1_Click()
Dim Number As Single, Answer As Single

'Input
If TextBox1.Value <> "" Then Number = TextBox1.Value 'If TextBox1 is not empty then Number = value in the textbox
If TextBox2.Value <> "" Then Number = TextBox2.Value 'If TextBox2 is not empty then Number = value in the textbox

'Process
 If TextBox1.Value <> "" Then Answer = 9 / 5 * Number + 32   ' Converts Celsius to Farenheit if textbox is not empty
 If TextBox2.Value <> "" Then Answer = (Number - 32) * 5 / 9 ' Converts Farenheit to Celsius if textbox is not empty
 
'Output
If TextBox1.Value <> "" Then TextBox2.Value = Answer Else TextBox1.Value = Answer 'Places answer in correct text box
End Sub

Private Sub CommandButton2_Click()
TextBox1.Value = ""
TextBox2.Value = ""
End Sub

Private Sub TextBox1_Enter()
TextBox1.Value = ""
TextBox2.Value = ""
End Sub

Private Sub TextBox2_Enter()
TextBox1.Value = ""
TextBox2.Value = ""
End Sub

 

D. Excel calculator challenge

Use the one way code, not the two way code!

Paste this code into each new userform you make. Also make a command button to open each new form on the worksheet.

Make a calculator using an Excel UserForm and the one way VBA code to

1. Determine the square root of a number.  eg. Sqr(16) returns 4

2. Determine the perimeter of a circle given the radius. Perimeter = 2 * Pi * Radius

3. Determine the area of a circle given the radius. Area = Pi * Radius * Radius

4. Convert feet to metres. Metres = Feet *12*2.54/100

5. Miles per hour to Km/hr. Kph = Mph * 1.609344  

6. Convert one currency into another. Current exchange rates.

eg. USD = 45.7473 * INR  'Converts US Dollars to India Rupees

7. Multiply two numbers together. Note: Three TextBoxes are needed

8. Solve quadratic equations (Super challenge)

 where "±" represents 

  and    

 

Attachment	Size
Calculators.xls	55 KB