Getting a specific Number Format in a TextBox of a UserForm

[e4excel]

Dear Gurus,

I am trying to get a specific Number Format which I always use for currency in all my files, I have created a user form and I need to get the same format in the user form however I am unable to do so..
My Number format is text starting with "Rs " and then followed by numbers with the commas at the desired places...Whiles googling I found something more interesting in the below link which formats while entering the text however I am not able to have the benefits of both..

My Number Format -

Please Login or Register  to view this content.

The LINK -

The Code I tried to tweak unsuccesfully.

Please Login or Register  to view this content.

Can someone please advise or help me on this code I want the Text in the TextBox as Number to be added seperately in the excel file where the contents would be saved.

Thanks in advance...God bless...

Warm Regards
e4excel

[Keebellah]

What's with the \ in the number format? I didn't know that the Rupees had a \ in it's format

[e4excel]

Its just the separator I too had found this solution on the net...

Please Login or Register  to view this content.

[xlnitwit]

The backslash indicates that what follows- a comma in this case- should be treated as a literal character.

I would suggest using the Exit or BeforeUpdate event of the textbox for the formatting rather than the Change event if possible.

[e4excel]

I want the Text in the TextBox to display Rs but while getting saved in the Excel Sheet it should just be a number which can be used for any mathematical operation..

[xlnitwit]

When your code writes the value to a cell you need only replace the formatting characters you added.

[Keebellah]

If you ste the cells format to Custom and paste this in it, the value will be without the characters

Please Login or Register  to view this content.

[e4excel]

I have used the code in my first code which I found however I wanted to combine the best of the original code with this format for Rupees as well as retaining the Number Value in the excel sheets for performing Mathematical Operation...

[Keebellah]

With the format code I indicated all you need to do is to past the numeric value form the textbox (without the Rs) into the cell. The formatting will display the Rs but there is no text in the cell, just numbers.

[e4excel]

I am unable to follow what you are trying to suggest ..I tried the code in the AfterUpdate it displays the text with the Rupees however gets entered as Text so no math operations can be performed.

Please Login or Register  to view this content.

I want to have the dual advantage of displaying it with Rupees as well as that being stored as a number when entered in a cell...

Please provide the code if possible... I think the NumberFormat is not a TextBox Property so if there's any other workaround which I am not aware of please suggest the same or the code...

[xlnitwit]

Do you have the textbox linked directly to the cell? If you do, you cannot have what you want. You must use code to write the value to the cell after removing the formatting.

[Keebellah]

True, but maybe you could it another way.
Is it possible you attach a sample workbook with the routine, some non-sensitive data would help, makes it easier to test under the conditions you are using it.