Set Cell to Always Display Four Digits With Two Constants
Howdy dudes and dudettes,
I am trying to set my Cell B4 to always display numbers in this particular format:
0010
0020
0030
0040
0050
0060
0070
0080
0090
0100
0110
0120
So if just a 1 is entered into Cell B4, it will display as 0010, a 12 would display 0120 and soforth.
I thought I might be able to set this as a cell default by adding a custom something-or-other in the Format Cells option, but I was unable to find something that would work.
What is the best way to solve this? Thanks!
Last edited by swordswinger710; 12-15-2010 at 09:41 AM.
Use a custom format of 0000 (Cells>Format>Custom)
Thank you, I did try that, but then it displays the numbers like this:
1 = 0001, 12 = 0012
instead of
1 = 0010, 12 = 0120
Is there a custom format to somehow make the number a two digit number with a zero on each end, or perhaps make it a three digit number with one zero at the right end? There will never be any numbers entered higher than 99.
Last edited by swordswinger710; 12-08-2010 at 12:20 PM.
Using code, like this perhaps? This goes in the sheet module - right-click on sheet tab, View Code and paste. If you enter something in col B it will format, but I don't know how to do with that custom formats (if indeed it is possible).
Please Login or Register to view this content.
Custom format:
000"0"
Everyone who confuses correlation and causation ends up dead.
The one thing I didn't try.
Or:
000\0
Hehe, you guys are keeping me hopping.Thank you! That works.. very nicely in fact. But now I have the issue of this solution breaking StephenR's formula here...
And all is well once more. Thank you so much yet again!
Aaand.. a bug has arisen.
This works all well and good as long as a two digit number is entered into Cell B4, but the instant the four digit number is entered the way it's supposed to be, it changes to a very wrong number:
I enter 88 into Cell B4, and it becomes 0880, the correct format.
I enter 0880 into Cell B4 because that's the correct format, then I get 8800, the very wrong format.
Help? Thanks.
You would have to use code for that.
Mmm, I thought I might. So may I ask for help with that?
Would I be correct in assuming that if you enter anything less than 4 digits, you want the 0 added to the end?
Well, the format is supposed to be 4 digits. The only ways a number will be added will be in either the correct 4-digit format, in which case nothing should change, or in a 1 or 2-digit format depending on the number, which should then change to the correct 4-digit number.
It should work like this:
A user wants to enter an 8.
It's supposed to be displayed as an 0080.
He enters 8, and it switches to 0080.
He enters 08, and it switches to 0080.
He enters 0080, and it stays the same.
He enters 18, and it switches to 0180.
He enters 0180, and it stays the same.
He enters 80, and it switches to 0800.
He enters 0800, and it stays the same.
Thank you so much again, if anything is still unclear, fire away.
You will need to format the relevant cells as Text, then right-click the sheet tab, choose View Code and paste this in, adjusting rngMonitor to whatever range is required.
Please Login or Register to view this content.
Hmm, I'm not sure what I'm doing wrong, but it's not working just yet.. I'm thinking it's something to do with the range? I only want this to work on Cell B12. (Cell B12:F12 to be exact).. I tried doing this:
and this:Please Login or Register to view this content.
and this?:Please Login or Register to view this content.
but none of that is working.. what's my error?Please Login or Register to view this content.
It should just be the first one - did you format it as Text first?
Yes, I did that.. here, perhaps you should have a look at it.. thank you.
You changed my code from a Change event to a (non-existent) format event so it's not surprising it doesn't work! It has to be a Change event, so you need to incorporate it into your existing event code.
For example (untested hair code):
Please Login or Register to view this content.
Oops, I didn't know that about the name.
What's the right way of combining these two Worksheet Changes so that they both work? Thanks again.
It works for me in your sample file.
Ah, okay, I see where I went wrong.This other code:
didn't contain the correct Range. It seems to work now! Thank you so much! Keep up the great work, it's fantastic.Please Login or Register to view this content.
Last edited by swordswinger710; 12-09-2010 at 02:16 PM.
Another oops.. can something be added to the code to hide the value when it's 0000? If the field is empty, there shouldn't be any zeros displayed.. if that could be solved yet that would be awesome! Thanks.
Oh bummer, I guess it shows the zeroes due to the fix to this issue here.. Hmm, would you mind taking a look at this? Thanks so much again..
Small alteration:
Please Login or Register to view this content.
Thank you very much, that works wonderfully, and to solve the zeroes showing on the other sheets, I just gave them formulas. I really appreciate the help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks