# 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?

### 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).

Macro assumptions:

• the ActiveCell number format is representative of the Selection
• the base cell format is General category, and any action will swith to Number category
• the cell format is either fixed (Fx) or with a thousands separator (,x). See lines 16 to 23 of code 1.
• there is no Currency or Accounting format in the Selection
• the font color for negative values is ignored

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.

• This example was developed in Excel 2016 Pro 64 bit.
• Revised: Saturday 23rd of June 2018 - 07:11 PM, [Australian Eastern Standard Time (EST)]