Excel Userform VBA problem - text box entry issues

SilverSixx

New Member
Joined
Jul 6, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
I'm new to VBA and I've come across something that's causing me a problem and hopefully someone can help me out.

I have 3 text boxes, one of which is a total cost and this figure is populated automatically. I have one text box which is where a currency amount can be typed in (a portion of the total cost) and another one where a percentage (again, a percent of the total amount) can be typed in.

What I am trying to do is to have the user either type in an amount which will them show the percentage of the total amount, or a percentage of the total amount which will then show the currency amount in relation to that percentage.

As an example:

The total amount is £1000.00. The user knows they will be paying £500.00 so that is typed in the currency text box and the percentage text box shows 50%.

I can do the calculations individually but when I run the form the code contradicts itself and I don't get the correct results.

Any ideas?
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Me.TextBox3.Value = Me.TextBox2.Value / Me.TextBox1.Value * 100
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Me.TextBox2.Value = Me.TextBox1.Value * Me.TextBox3.Value / 100
End Sub
 

SilverSixx

New Member
Joined
Jul 6, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Me.TextBox3.Value = Me.TextBox2.Value / Me.TextBox1.Value * 100
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Me.TextBox2.Value = Me.TextBox1.Value * Me.TextBox3.Value / 100
End Sub
Hi & welcome to MrExcel.
How about
VBA Code:
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Me.TextBox3.Value = Me.TextBox2.Value / Me.TextBox1.Value * 100
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Me.TextBox2.Value = Me.TextBox1.Value * Me.TextBox3.Value / 100
End Sub

Hi,

Thanks for the reply, I'll give it a try. How I've tried it previously is to create a Sub like:

VBA Code:
Sub HealthSplitCurrency()

    Dim c As Double, d As Double
        On Error Resume Next
            c = txt_Amount£.Value
            d = txt_TotalCost.Value
                On Error GoTo 0
                    txt_AmountPercent.Value = Format(c / d, "Percent")

Then run it in a Private Sub allocated to each of the text boxes. As they are set to work AfterUpdate they cancel each other out.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,822
Members
415,859
Latest member
Vain

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