toggling between styles

[lior03]

hello
i want to present data in various styles.

selection.Style = "jon"


this style round a figure to the the nearest 1,000.
i want to add a botton which will toggle between this style and a normal style which will present figures as they are.
one click - 'jon' one click - normal and so on.
thanks

[Killian]

Then your click event should run something likeWith Selection
If .Style = "Normal" Then
.Style = "jon"
Else
.Style = "Normal"
End If
End With

[lior03]

hello
as i said i have a style named jon.i'ts number format is:
#,###,",000"
i am trying to round figures using this style to the nearest thousenth.
why do it round

96,45723,46544,796164,718
to
96,00023,00045,000165,000
is there a way in vba to do the roundig correctley?

[lior03]

what's wrong with:
If selection.Formula = "=SUM(ROUND(R[-2]C:R[-1]C,-3))" Then
selection.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Else
selection.Formula = "=SUM(ROUND(R[-2]C:R[-1]C,-3))"
End If
assumig i want to present information in various ways- rounded or not.
thanks

[mdmackillop]

Hi Moshe,
Excel number precision is limited to 15 digits.

[lior03]

hello
how can i enable the user select a number format he want to present data in just one click?

[lenze]

Try using the Before-DoubleClick event instead.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count > 1 Then Exit Sub
If Target.Style = "Normal" Then
Target.Style = "Jon"
Else: Target.Style = "Normal"
End If
Cancel = True
End Sub
Your style is correct if formatted as indicated. It rounds 45723 to 46,000
If you don't want the 000s, change the style format to #,"M" which rounds 45723 to 46M.

HTH

lenze