How to programmatically add a non standard custom cell format

[tiamat]

Hi,

I have a value in a cell I.e. 123456 that I wish to display to 3 significant
figures I.e. 123000.

My question is this : I wish to present the significant value as a display
format and not change the underlying value.

So far as I am aware the custom cell format codes do not include a
significant figure code. I.e. #, etc..

I can use either a UDF or macro that can return the value to the required
significant figures.

So any ideas? Is there a way I can do this I.e. have 123456 in the cell but
display 123000. Note the cell value is not a fixed width I.e. it could take
values such as 0.0001234, 1234, 1234.123, etc. and be displayed as 0.000123,
123, 1230.

 

[Rick Rothstein \(MVP - VB\)]

Does this...

#,"000"

or perhaps this (if you want to retain the "leading" zero for values less
than 1000)...

0,"000"

custom format do what you want (use them exactly as shown... with the quote
marks)?

Rick

 

[tiamat]

Hi Rick,

Unfortunately not, as that takes the thousands part and then appends the
text. The cell value can take any value, so the above would work only if the
scale was set.

However, that does lead me to thinking about using a multi conditional
format which uses scale i.e. 0-9, 10-99, 100-999, but that would be a bit
ghastly.

Ideally, I would like to call out to my own UDF function/Macro that returned
back a string for the value. If this is possible?

 

[Rick Rothstein \(MVP - VB\)]

Can you give us examples of the various type of values (0-9, 10-99, 100-999,
etc.) and show us how want them all to look? Also, can there be negative
values?

Rick

 

[TomPl]

Try This

Sub try()

ActiveCell = WorksheetFunction.Round(ActiveCell, -3)

End Sub

 

[TomPl]

Sorry, that changes the value. But if you could just reference the value
from another location, the formula would work fine in a spreadsheet.

Tom

 

[Peter T]

That wouldn't be necessary. In a change event could apply the cell's number
format to display as required leaving the underlying value unchanged, eg

d = theCell.Value ' d = 123456

' bunch of code to get the right format

s = """123000"""

theCell.NumberFormat <> s then theCell.NumberFormat = s

At the moment I think Rick is waiting for the OP to come back with further
details

Regards,
Peter T

 

[tiamat]

Thanks for the responses guys.

Firstly, to Rick
"Can you give us examples of the various type of values (0-9, 10-99, 100-999,
etc.) and show us how want them all to look? Also, can there be negative
values?"

Thanks for the interest but as you probably figured if one includes negative
values and taking Excel's precision of 15. Then that would mean 15 * 2 = 30
conditional format combinations and 14 more I believe if precision is for
example 0.00000000000000123456789112345. I have probably got the numbers
wrong but throw in negative numbers and worse case scenario that is 88
conditional formats. Thats why I thought it was a bit ghastly and discounted
it. I probably got the value extent wrong but you get my drift.

This one is at Peter :
This is actually a very interesting idea. Depending on where you capture
it. It would be possible after I have written the values to the grid to then
explicitly overwrite the number format as a second stage. I will give that a
go.

 

[tiamat]

At : Peter

That did the trick works like a charm, thank you very much

 

[Rick Rothstein \(MVP - VB\)]

Firstly, to Rick

"Can you give us examples of the various type of values (0-9, 10-99,
100-999,
etc.) and show us how want them all to look? Also, can there be negative
values?"

Thanks for the interest but as you probably figured if one includes
negative
values and taking Excel's precision of 15. Then that would mean 15 * 2 =
30
conditional format combinations and 14 more I believe if precision is for
example 0.00000000000000123456789112345. I have probably got the numbers
wrong but throw in negative numbers and worse case scenario that is 88
conditional formats. Thats why I thought it was a bit ghastly and
discounted
it. I probably got the value extent wrong but you get my drift.

Why are you placing restrictions on what can and can't be done? In your
first post, you mentioned the possibility of using VBA code to accomplish
what you want... there are not very many restrictions on what you can and
cannot do with VBA code. I was not thinking of using conditional formats,
rather, I figured if the cell format I posted wouldn't work for you, then I
would attempt some event code to do what you want. The key for me (and the
other volunteers here I would guess) is to understand your question in full.
With that in mind, can you provide the example values and results you want
from them that I originally asked you for? And, if you would like to handle
negative values, indicate that and provide samples for them if the results
for negative values are not simply the same as for the positive ones except
for their minus signs.

Rick

 

[Peter T]

Well you got that working pdq !
I was just about to give it a go and quickly found it turning out to be
quite involved, to cater for various places, negative and presumably
rounding. Anyway glad you've got it working.

Regards,
Peter T

 

[Rick Rothstein \(MVP - VB\)]

Why are you placing restrictions on what can and can't be done? In your
first post, you mentioned the possibility of using VBA code to accomplish
what you want... there are not very many restrictions on what you can and
cannot do with VBA code. I was not thinking of using conditional formats,
rather, I figured if the cell format I posted wouldn't work for you, then
I would attempt some event code to do what you want. The key for me (and
the other volunteers here I would guess) is to understand your question in
full. With that in mind, can you provide the example values and results
you want from them that I originally asked you for? And, if you would like
to handle negative values, indicate that and provide samples for them if
the results for negative values are not simply the same as for the
positive ones except for their minus signs.

Okay, I see you got it working using Peter's suggestion (which was roughly
what I had in mind for an event code solution)... Good! No need to post the
examples I asked about then.

Rick

 

[Peter T]

Although you didn't explicitly say I kind of assumed what subsequently
appears to have become my suggestion is what you had in mind earlier when
you asked the OP to give some more details.

In hindsight, although the example I gave might be required for some number
types, in the code for any six digit integer I would have applied your
suggestion for a numberformat

s = "#,""000"""

Regards,
Peter T