Formatting cell based on cell contents

[msog81]

Dear all,
I have looked for a solution on this and I can't seem to find one. My request is fairly simple but I am new to VBA programming and can't quite get my head around it.
Basically I want to have a VBA code (cell or worksheet change event?) that operates in one column in a sheet e.g. C2:C1000 and keeps the number of characters that are entered in that cell to three, prefixing any value less than 3 characters with zero's. There will never be reason more than 3 numbers will be entered into this column.

It doesn't matter to me (I dont think) if the resultant is a text string or numbers.

For example.
If I enter "3" into C2 I would like it to automatically change to "003"
If I enter "32" into C2 I would like it to automatically change to "032"
If "321" is entered I would like it to stay as "321"
And then the same throughout the whole column down say 1000 rows or so.

I could do it with formulas in a different cell but I want to keep the sheet streamlined.

If it is possible I would be really appreciative of the help!

Thanks in advance!

[MoParker]

Hi msog81,

There easiest way to add leading zeros to numbers is to convert it to text. Have a look at the code below:

Please Login or Register  to view this content.

This code will:
- set the target cell to text
- add zeros to the front of the number if required-
- ignore the annoying number stored as text error

To apply the solution to a range just run the following code:

Please Login or Register  to view this content.

And to automatically run the code when a cell in the range is changed, just add this code in the respective worksheet object in VB editor:

Please Login or Register  to view this content.

Let me know if that helped!

Cheers,
Mo

[msog81]

Thanks for the quick reply MO!
Could you give me some pointers on how you combined the codes to get it to run, every time I try I get either "run-time error '91': Object variable or With block variable not set" or "Run Time error '28': Out of Stack Space". Thanks again.

[JOHN H. DAVIS]

Try this file Sheet1 Column C and see if it works for you.

[msog81]

Thanks John, that works PERFECTLY, thanks so much for taking the time to write to up for me.

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. Please mark this thread as solved.