xlf QandA series

Change the number of worksheet decimal places from the keyboard

QUESTION

  1. From: Bethel
  2. Location: Melbourne, Australia
  3. Date: 3 August 2016

IS THERE A KEYBOARD SHORTCUT TO CHANGE THE NUMBER OF DECIMAL PLACES IN A RANGE?


Answer icon Answer

1. A VBA keyboard shortcut


The VBA macro AdjustDPplus in code 1, when assigned to a key combination such as Ctrl + Shift + p, will increment one decimal place to a maximum of 9. Just keep tapping the p key to increment. This is equivalent to manipulating the Decimal places property in the Number category on the Formal Cells dialog box Number tab (figure 1).


decimal change
Fig 1: Format Cell dialog box, Number tab, Number category - showing Decimal places, and Use 1000 Separator (,)

The AdjustDPplus macro


Macro assumptions:



Code 1: Macro AdjustDPplus, when assigned to a key combination such as Ctrl + Shift + p, will increment one decimal place to a maximum of 9. 
Sub AdjustDPplus()

Dim AC As Range
Dim Form As String
Dim NumType As String

Set AC = ActiveCell
' Assume the ActiveCell number format is representative of Selection
Form = AC.NumberFormat
NumType = Evaluate("Cell(""Format""," & AC.Address & ")")

    If Form = "General" And Application.IsNonText(AC) Then
        AC.NumberFormat = "#,##0.00"    ' << SET DEFAULT - general to specific decimal places
    ElseIf Application.IsNumber(AC) Then
        Select Case NumType
            Case "F0"
                Form = Form & ".0"
            Case "F1" To "F9"
                Form = Form & 0
            Case ",0"
                Form = Form & ".0"
            Case ",1" To ",9"
                Form = Form & 0
        End Select
        Selection.NumberFormat = Form
    End If

End Sub

Selected sections of code 1 by line number:

  1. Line 9: Suppose that the number format of the active cell is set to General, as displayed in the Number group on the ribbon (shortcut N), then NumberFormat property returns the text string "General"
  2. Line 10: Call the Excel Cell function with argument "Format" and address of the Active Cell in \$A\$1 style. The Cell function is not available on the Application.WorksheetFunction list. Instead, the Excel ver 4.0 Evaluate macro function is used to convert the string to a formula. Within the constraints of the model, NumType is either {"F0", "F1", "F2", …} in the case of no thousands separator, or {",0", ",1", ",2", …} with a thousands separator
  3. Line 12: If the Active Cell has "General" number format and is not text or a formula that returns text, then apply the default format of two decimal places with the comma thousands separator. Adjust this line to your default preference
  4. Line 14: Else, if the Active Cell is a number, then execute the Select Case statements in lines 15 to 24
  5. Lines 16 to 19: If the Active Cell has "Number" number format in the series {"F0", "F1", "F2", …, "F9"}, then increment one decimal place. For example, "F2" is equivalent to "0.00"
  6. Lines 20 to 23: If the Active Cell has "Number" number format in the series {",0", ",1", ",2", …, ",9"}, then increment one decimal place. For example, ",00" is equivalent to "#,##0.00"

Selected VBA functions used in this module.


VBA functions Description
Address([RowAbsolute,] [ColumnAbsolute,] [ReferenceStyle,] [External,] [RelativeTo]) All arguments are optional. RowAbsolute default = true, ColumnAbsolute default = true, ReferenceStyle default is xlA1
Returns the absolute address of the Active Cell in A1 reference style. \$B\$2 in figure 2
Evaluate methodEvaluates a string as if it were a formula
NumberFormat Excel's Format Cells, Number category returns:
No 1000 separator: {"0", "0.0", "0.00", …}
With 1000 separator: {"#,##0", "#,##0.0", "#,##0.00", …}


Selected Excel functions used in this module.


Excel functions Description
CellReturns information about the cell based on info_type parameter. If info_type is "format"
Excel's Format Cells, Number category returns:
No 1000 separator: {"F0", "F1", "F2", …}
With 1000 separator: {",0", ",1", ",2", …}
IsNonText(value)Returns TRUE if value or reference is not text (including blank).
IsNumber(value)Returns TRUE if value or reference is a number.

Clearly, NumberFormat and Cell(Format,Ref) are complimentary


It is left to the reader to develop the AdjustDPminus version


2. The Excel ribbon shortcut


There are Increase Decimal, and Decrease Decimal shortcuts in the Number group on the Excel ribbon

  1. Press Alt H 0 to increase decimal (show more decimal places)
  2. Press Alt H 9 to decrease decimal (show fewer decimal places)

The zero-nine items are shown in the Number group of the ribbon in figure 2. Each action has a one step limit, and the sequence must be repeated for each increment or decrement.


decimal change
Fig 2: Alt H 0 9 items (circled) - 0 to increase one decimal place, and 9 to decrease one decimal place