Decreasing/Increasing decimal places

[Daniel Bonallack]

One of my colleagues has asked for a macro that will
increase or decrease the number of decimal places of a
number. I know the toolbar button does this very nicely,
but he wants a short-cut key.

So if the number is 54.37, he wants code (executed by
shortcut keys) that will decrease/increase the decimal
places by one each time he runs it.

Thanks in advance for any help.

regards
Daniel

 

[Tom Ogilvy]

How would the macro know whether he wants to increase or decrease the number
of decimal places.

Basically you would need to detect the number of zeros on the right of the
decimal point for the numberformat

Sub testDecimal_Add()
If Not IsNumeric(ActiveCell) Then Exit Sub
sStr = ActiveCell.NumberFormat
If InStr(sStr, ".") Then
iloc = InStr(sStr, ".")
numDecimal = Len(sStr) - iloc
ActiveCell.NumberFormat = Left(sStr, iloc) & String(numDecimal + 1, "0")
Else
ActiveCell.NumberFormat = "#.0"
End If


Sub testDecimal_Subtract()
If Not IsNumeric(ActiveCell) Then Exit Sub
sStr = ActiveCell.NumberFormat
If InStr(sStr, ".") Then
iloc = InStr(sStr, ".")
numDecimal = Len(sStr) - iloc
If numDecimal > 0 Then
ActiveCell.NumberFormat = Left(sStr, iloc) & String(numDecimal - 1, "0")
End If
Else
ActiveCell.NumberFormat = "#.0"
End If
End Sub

Might be a start.

Regards,
Tom Ogilvy