Incorrect Formatting Applied to Data from User Form to Worksheet

Hi

In my attached sheet I have a user form that extracts data from the FFP worksheet when the user form opens and then you can save data to the FFP worksheet and calculate a value and display the value on the user form itself while the user form is still active.

I please need help with the following formatting when clicking the CALCULATE and SAVE buttons on the user form:

1. User Form Data entry fields (3,4,6) are percentage values. The question is how do you keep it as a percentage value when it gets transferred to the FFP worksheet when the user enters either a numerical value e.g. “9” or a percentage value “9%” and then click the calculate button? In other words what would the VBA code say – if a 9 is entered in entry field (3) on the user form then it would convert it to 9% but also if 9% is entered then it would keep it as 9%?

2. User Form Data entry field (5) is a currency value, but when it is saved to the FFP worksheet by clicking the save or calculate button it saves it as a “text” value and not currency value and therefore the Answer throws out a #VALUE! Error. In the VBA code how do you keep it in currency format?


Thanks

What is the decimal separator in your system: "dot" or "comma" ?

It is a "dot"

For example, for "CalculateButton":

Due to an undefined security alert, you have the code in the txt file below.

Thanks for the code, this is great! All the "percentage" values work correctly now.

However, I picked a problem with "Reg7" input field - when you open the User Form and immediately click CalculateButton then it deletes the original value and changes "Reg7" input field to 0 (zero).
This also happens when you only change any one of the percentage values and click the CalculateButton then it changes the currency field (Reg7) to zero where it should not change this field at all, the original value should remain..

I really appreciate your help with this!

I have attached a new workbook that runs your code if you would like to see what it does as I explained above.

What currency format you have for Reg7/"IncAfterTax"/"Answer" fields (?), how it looks visually (?), make and upload a screenshot.
With my regional settings I can not reproduce such a situation.

Originally Posted by porucha vevrku

I did not mean such detailed settings as shown above, but only the appearance of currency values, e.g.:

10,000,254.25$
10,000,254.25 $
$10,000,254.25
$ 10,000,254.25
10 125 125.458$
10 125 125.458 $
$10 125 125.458
$ 10 125 125.458
10.545.547,48 $
etc.

So remove these "details" (this image) as soon as possible.

Ah OK... I removed the image

I am using excel 365 but when I run the same worksheet on another computer using Excel 2010 It also does the same thing

Originally Posted by porucha vevrku

I did not mean such detailed settings as shown above, but only the appearance of currency values, e.g.:

10,000,254.25$
10,000,254.25 $
$10,000,254.25
$ 10,000,254.25
10 125 125.458$
10 125 125.458 $
$10 125 125.458
$ 10 125 125.458
10.545.547,48 $
etc.

So remove these "details" (this image) as soon as possible.


...

Edit:

And how will it be with it ?

Thanks I will try this code and let you know shortly

I used this code (FFP_CalculateButton_02.txt‎) and still when I initialize the User Form and immediately click the Calculate Button without changing any field it changes the R10,000.00 in Reg7 now down to R10 and not R0.00 anymore

Originally Posted by porucha vevrku

And this is what your currency format looks like => R10,000.00 ?

It actually looks like R10 000.00 (without the comma)

Everything is working fine too:

Originally Posted by porucha vevrku

Everything is working fine too:

Yes I got the same results. I have no idea why it would be doing it's own thing and changing Reg7 value when clicking the Calculate Button without making any input changes or when only changing the "percentage" values and then clicking calculate. None of the "percentage" values change to some strange value when clicking the Calculate Button

You say you don't get this problem there by you?

Apparently the differences in system and regional settings are greater/larger than I thought, I can't reproduce this behavior of variable values ​​in my place. If the amendment/correction below doesn't work for you, I'm afraid I won't help you.

Ok thanks for the help and the effort!

What I have figured out so far:
1. In region setting in Control panel I have to select the comma under Digit Grouping Symbol then the code works right Showing R10,000.00 and it does not change the value when clicking Calculate Button. If I don't select the comma and have it show R10 000.00 then the code changes the value from R10 000.00 to R10
2. If I select any other country region in control panel (e.g. United Kingdom or United States) which already has the comma selected under Digit Grouping Symbol then the sheet changes $10,000.00 to $0.00 or £10,000.00 to £0.00 when I click the Calculate Button. I assume this is due to the "static" region setting in the code (see attached)?
If this is so then nobody from other countries would be able to use this worksheet as it would not work correctly for them and this would not help my future plans with this type of sheet?

Not sure what else to do now about this code.png

Originally Posted by Rudidw

... If this is so then nobody from other countries would be able to use this worksheet as it would not work correctly for them and this would not help my future plans with this type of sheet? ...

In the most "drastic cases" ... ... this can happen.
You must remember that this is not only about the settings of the type: "comma, period, such a currency, other currency" - this also applies to the matrix of characters / code page on which the operating system works.
English-speaking countries have a different set of characters than, for example, French-speaking, German-speaking, Slavic-speaking, Greek-speaking, etc.

Try to "practice" your case with the following set of functions in vba - maybe one of them will solve your problem (with your system's regional settings and your code page):

Originally Posted by porucha vevrku

Apparently the differences in system and regional settings are greater/larger than I thought, I can't reproduce this behavior of variable values ​​in my place. If the amendment/correction below doesn't work for you, I'm afraid I won't help you.

Thank You, Thank You, Thank You!!!

This last code of yours works like a charm, does exactly what I want and without any of the previous problems! I have now tested it multiple times and with different regional settings and all works 100%

That was frustrating but thank you for your persistence to get my vba problem solved, I am truly grateful!!!