VBA Userform, multiplying number with percentage

[gulli09]

I'm designing a userform. I'm multyplying two textboxes together, one of the textboxes is a number and the other one is a percentage.

First of all I had problems changing the format to a percentage. If there is an easier way to change the format that works that would be great as well. The code currently works that way that as soon as both the percentage and the number is in place they multiply together in another textbox. When I change the format to a percentage the calculations aren't able to recognize the percentage for a percentage and return nothing(eg. when the % is added, it's calculated as number*nothing, and thus returning nothing because it doesn't read the percentage as a number as soon as the % is added)

I basically need to trick the calculator into beliving there is no % sign, whilst at the same time showing the % sign to the user.

In a nutshell it's one textbox were the users enters f.x. 15, and the outcome is 15% he then enters a number, f.x. 100 and then in a 3rd textbox it calculates 15%*100 returning 15. What would be the best way to achieve this? I can provide the code if nescessary.

 

[Norie]

Try this.

Textbox3.Value = Left(Textbox1.Value, Len(Textbox1.Value)-1)/100 * Textbox2.Value

 

[gulli09]

That would give me the desired outcome, but at the same time I need the percentage field to show a percentage % sign. Is there no way to format the percantage in a way for me to be able to multiply it with the number textbox without problem?

 

[Norie]

I thought you had formatted the first (percentage) textbox with a %?

This is how I did it.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    TextBox1.Value = Format(TextBox1.Value) & "%"
End Sub

 

[gulli09]

With this code I do get a % sign, but the calculations return nothing. Have you tried your code and succesfully managed to multiply the % with the number?

 

[Norie]

Yes.

 

[gulli09]

For some reason it wouldn't work for me, I appreciate the quick answers and the help. Ended up with quite a few loop holes to get the calculate the percentage with the number, keeping the % whilst still calculating:

Private Sub

Dim isIncluded As Boolean
isIncluded = False
Dim tempText1 As String
tempText1 = txtText1.Text

isIncluded = InStr(tempText1, "%")

If isIncluded = True Then
tempText1 = Replace(tempText1, "%", "")
End If

If Not txtText1.Value = "" And Not txtText2.Value = "" Then
txtMulti1 = ""
txtMulti1.Value = CDec(tempText1 / 100) * CDec(txtNum2.Value)
txtMulti1.Value = Replace(txtMulti1.Value, ".", ",")
End If

2 clarify, it now works using this code, just in case anyone had the same problem and the code above didn't work for them either!

 

[mikerickson]

If TextBox1 holds the percentage and TextBox2 the other multiplicand and you want their product put in TextBox3 when CommandButton1 is pressed, you could use code like this.

Private Sub TextBox1_AfterUpdate()
    With TextBox1
        .Text = Format(Val(Replace(.Text, "%", vbNullString)) / 100, "#.0# %")
    End With
End Sub

Private Sub CommandButton1_Click()
    TextBox3.Text = Val(Replace(TextBox1.Text, "%", vbNullString)) / 100 * Val(TextBox2.Text)
End Sub

 

[Norie]

gulli09

What's decimal/thousand separators do you use?

 

[gulli09]

"," for decimal and "." for thousand

Hence the mix with replacing it, because I needed further decimal places.

I appreciate all the help, but with the code working flawlessly as it is, I'm quite happy to keep it this way, ****mix or not