Calculator

KyleG

Well-known Member
Joined
Jun 12, 2004
Messages
623
I want to create a calculator that works with three figures. Essentially i want to insert any two of the three values and when the enter button is pushed for the third to be calculated.

At this stage i have set up a userform with 3 text boxes and a button.

I need the button to first identify what boxes have values and then calculate the result and insert it into the third text box. A msg needs to be generated if all three boxes have values or if only one box has a value.

What i am calculating is turnover, wages and wage %.

Wages/Turnover * 100 = wage %

wage %/100 * Turnover = Wages

100/wage% * wages = Turnover

Thanks
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

KyleG

Well-known Member
Joined
Jun 12, 2004
Messages
623
So far i have come up with 8 cases that have different actions performed if they are met.

Textbox1,2,3 Blank or
Textbox1,2 Blank or
Textbox2,3 Blank or
textbox1,3 Blank
Msgbox "Need more data"

Textbox1 Blank
100/Textbox3* Textbox2

Textbox2 Blank
wage %/100 * Turnover

Textbox3 Blank
Wages/Turnover * 100

Textbox1,2,3 non-blank
Textbox1,2,3 clear selection

Now how do i word that in code?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Can you tell us which of the three values is in which textbox?
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Try using the Case Select method!

Using a Change Event test for values in the boxes/cells [If x > "" Then myTest = myTest + 10] do this test for each box/cell change the value added to the myTest var. for each box/cell type. Make that myTest value the "Case" value to direct your actions!
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Give this a try:

Code:
Private Sub CommandButton1_Click()
Dim tlen1 As Integer, tlen2 As Integer, tlen3 As Integer
tlen1 = Abs(Len(TextBox1.Text) > 0) * 2
tlen2 = Abs(Len(TextBox2.Text) > 0) * 4
tlen3 = Abs(Len(TextBox3.Text) > 0) * 8
Select Case tlen1 + tlen2 + tlen3
    Case 6
        TextBox3.Value = TextBox2.Value / TextBox1.Value * 100
    Case 10
        TextBox2.Value = TextBox3.Value / 100 * TextBox1.Value
    Case 12
        TextBox1.Value = 100 / TextBox3.Value * TextBox2.Value
    Case Else
        MsgBox "You must enter 2 of the 3 values"
End Select
End Sub
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
If you test for a value and assign a value to each box test, like: Box1 is 10, box2 is 20 and box3 is 30. Then the combination of values added to myTest will reflect which box's are used and that myTest value will load the "Case" and trigger the correct Case code making the calculation and placing the correct value in the correct remaining box!
 

KyleG

Well-known Member
Joined
Jun 12, 2004
Messages
623

ADVERTISEMENT

That is absolutely perfect!

Thanks heaps.
 

KyleG

Well-known Member
Joined
Jun 12, 2004
Messages
623
Ok have made an alteration of what happens. Now i have a value only calculated for textbox3.

Private Sub CommandButton1_Click()
Dim TWP As Integer
TWP = 2#
TextBox3.Value = ((TextBox1.Value * TWP) / 100) - ((TextBox1.Value * TextBox2.Value) / 100)
End Sub

I have now got 18 option boxes. What i want is for different values for TWP to be set depending on what option button is selected. How would i go about doing this?

Thanks
 

KyleG

Well-known Member
Joined
Jun 12, 2004
Messages
623
I know that it can be set up with 18 cases but i have no idea how to even begin case statements.

Any help would be much appreciated.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
The Case statement has 4 parts:

The "Select Case Variable"
Select Case is the command, what the Case becomes is indicated by the "Variable." You load the variable with the result of a test or selection indicator. In your case you will be testing for a button click which will load a common Variable with the unique number or name of that button then run the Case. This statement starts the Case selection process. Note: The variable may need it's own set statements or Sub(s) to become loaded with values.

The "Case ValueOfVariable"
Case is the command and it tests for the specific value of the "Select Case Variable." Your run code goes below this statement. A new Case and code block is made for each Case your variable may contain!

The last Case ValueOfVariable is the "Case Else" statement which acts only if the Variable has a value that is not accounted for in the blocks of "Case ValueOfVariable" code.

The Select Case must be ended with the "End Select" command. You may Run other Sub(s) or use GoTo's within the Case blocks as needed!

From this point on it's cookbook, just add your info!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top