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:

Hello sir,

How to make sum in Specific Date range excel Vba