# xlf QandA series

## Change the number of worksheet decimal places from the keyboard

### QUESTION

- From: Bethel
- Location: Melbourne, Australia
- Date: 3 August 2016

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

### Answer

### 1. A VBA keyboard shortcut

The VBA macro `AdjustDPplus`

in code 1, when assigned to a key combination such as , 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).

### The AdjustDPplus macro

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:

**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 ), then**NumberFormat**property returns the text string "General"**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**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**Line 14:**Else, if the Active Cell is a number, then execute the Select Case statements in lines 15 to 24**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"**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 method | Evaluates 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 |
---|---|

Cell | Returns 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

- Press
**increase decimal**(show more decimal places)
to - Press
**decrease decimal**(show fewer decimal places)
to

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:**Thursday 11th of August 2016 - 04:47 PM, [Australian Eastern Standard Time (EST)]