User Form for Calculations in Excel

User Form to perform specific calculations for energy savings
A question on how to perform calculations in a user form by one of our website visitors, Marshall

Dear Dinesh,
I want the user to input the Gas Units in a user-form – say 12724 and have this multiplied by a fixed number, say 3.521p and divided by 100 to get pounds.
Now input the Gas day rate – say 20.71p and have this multiplied by 365 and divided by 100 to get pounds.
The results of these 2 calculations is automatically shown in the Total Gas per year text box.

Repeat this process for Electric with the results for the 2 calculations shown in the Total Electric per year text box: Electricity Day rate is 28.31, Electricity units are 6350.

Finally, these 2 results shown automatically in the Total Gas & Total electric box by adding them together.
Marshall

The details of creating an user form have been explained earlier. The main thing to remember about text-boxes is that they hold text data even when you enter numbers. The data has to be converted into numbers using the ‘val’ function.

The VBA code:
Private Sub CommandButton1_Click()
TextBox5 = Val(TextBox1) * 3.521/100 + Val(TextBox2) * 365/100
TextBox6 = Val(TextBox3) * 9.944/100 + Val(TextBox4) * 365/100
TextBox7 = Val(TextBox5) + Val(TextBox6)
TextBox7.Text = Format(TextBox7.Text, £#####.00″)
End Sub

Watch the video below to see how this is done quickly and easily: