VBA Userform textbox numbers formating

[oldman33]

I have a userform with several textbox (tb1, tb2, tb3). When the form opens, the textbox are populated from values in a worksheet:

Private Sub UserForm_Initialize() 
    [INDENT]tb1.Text = Format(Worksheets("Input").Cells(1, 1), "$#,##0")[/INDENT] 
    [INDENT]tb2.Text = Format(Worksheets("Input").Cells(2, 1), "0%")[/INDENT] 
End Sub 

The code works, but since the textbox are being populated with numbers I'm assuming the code needs to be changed to remove the "text" part. I tried entering "number" instead of text but got an error message. (Note- "N/A" is a possible option for the values)

Is there a code for the textbox to keep its formatting when a user enters in a new number? (If they enter 12345 into tb1, the textbox goes to $12,345 when the user clicks outside of the textbox)

For tb3, I need a code that will add tb1 and tb2. If the user changes either tb1 or tb2, tb3 will automatically update

Thanks!

[cytop]

A textbox is just that - a box to store/edit text. The .TEXT property returns the contents of the textbox as a string, it has no .NUMBER property. It does have a .VALUE property but that just reflects the 'value' of the .TEXT property as text... and vice versa.

Also, It has no inbuilt formatting so you're going to have to do it all yourself, unfortunately. The code should go in the EXIT event handler

If you want to format the text as numbers, you'll have to add code to the EXIT event:

Private Sub TB1_Exit() 
     
    TB1.Text = Format$(TB1.Text, "0.00") 
     
End Sub 

To add the contents of TB1 and TB2 to TB3 you need to add code to either the EXIT or CHANGE event handlers of both textboxes. If you add the code to the EXIT event handler, TB3 will be updated as TB1 or TB2 loses focus. If you add to the CHANGE event, then TB3 is updated every time either 1 or 2 is changed. Going back to what was said earlier, the contents of a text box is text, not a number, so you have to explicitly convert in any calculations.

Example:

 
Private Sub tb1_Change() 
     
    tb3.Text = Val(tb1.Text) + Val(tb2.Text) 
     
End Sub 

The same code goes in the TB2 Change event handler...

[oldman33]

Thanks for the response cytop!

The issue with the text box being for text seems a little too obvious. Thanks for the codes and advice. I'll give it a shot and see what happens

[oldman33]

I got the userform initialization to work properly. However I run into problems running the userfom. Instead of writing out all of the code, I've attached my file.

The userform is a little more complicated than initially described. There are now 4 textbox (tb1, tb2, tbP1, tbP2) and a label (I changed the textbox tb3 into the label tbTotal). tbTotal = tb1 / tbP1 + tb2 / tbP2. tb1, tb2, tbTotal are formated as "$#,##0" and tbP1, tbP2 as "0%"

The goal is that the userform will load with tb1= Sheet!B1, tb2 = Sheet1!B2, tbP1 = Sheet!A7, tbP2 = Sheet!A8. The output of the userform replaces the values on the sheet with the values in the in the userform (if the user doesn't change any values, the values remain the same).

It will probably be best for you to run the model yourself to see my flaws, but here are some of my problems:

When the user clicks on tbP1 and tbP2 then clicks elsewhere without changing the anything, the values remain unchanged. tb1 and tb2 however revert to 0. tb1 and tb2 need to be fixed

tbTotal initially calculates the proper total but once the user clicks on any textbox the value reverts to 0, not the new total.

Lastly, the model is not outputting the proper values. This is probably related to the above problems but could be a problem of its own.

-------
I will keep working on this too and I'll let you know if I make any progress. Thanks in advance to all those who take a crack at this!

[cytop]

The problem is to do with the $ and % signs interfering with the conversion from text to numbers...

Can you explain the logic, please - I'm having a stupid night.

[oldman33]

I want to take the currency values in B1:B2 (eventually there will be roughly 20 values in all but lets start with 2 and I will expand it from there) and divide them by the percents in A7:A8. So this results in B1/A7, B2/A8, and so on. These new values are listed in B7:B8. B9 contains sum(B7:B9). This is part of a much larger model that I can explain a little more if you would like me to.

The user has the option of going to B1:B2 or A7:A8 and manually changing these values directly on the worksheet. However I am creating this userform to make it more convenient for users. In this userform, B1 = tb1, B2 = tb2, A7 = tbP1, A8 = tbp2

The userform lists all the currency values with its respective percentage next to it. The total label on the userform is only there for the connivence of the user to show what the value of tb1/tbP1 + tb2/tbP2 is.

The user has the option to change any of the currency or percentage values in the userform. When they hit the run command button, the values in the userform replace the original values in B1:B2 and A7:A8. B7 has the formula =B1/A7 and B8 =B2/A8. B9 =sum(B7:B8). I've left these cells as formulas so that the user still has the option to manually change the other cells

Hopefully this is enough information. Let me know

Thanks!