Calculator

[KyleG]

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

 

[KyleG]

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]

Can you tell us which of the three values is in which textbox?

 

[Joe Was]

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]

Give this a try:

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]

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]

That is absolutely perfect!

Thanks heaps.

 

[KyleG]

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]

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]

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!