• 511K Members
• 6,074 Online
• 608K Conversations

## Decimal Point Formula - Autopopulate

Occasional Visitor

# Decimal Point Formula - Autopopulate

I need a formula or something in VBA that will help me do mass changes to the amount of decimal points allowed in a cell. I'd like to be able to set the amount of decimal points in one cell, and then it will change it across all of my sheets.

2 Replies

# Re: Decimal Point Formula - Autopopulate

Hello @krbuck0,

Here is an example:

Sub Dosomething()
Dim xSh As Worksheet
Application.ScreenUpdating = False
For Each xSh In Worksheets
xSh.Select
Call RunCode
Next
Application.ScreenUpdating = True
End Sub

Sub RunCode()
Range("A1:A5").Select
Selection.NumberFormat = "0.00000"
End Sub

Just change the text in red to fit your specific range and number format.

# Re: Decimal Point Formula - Autopopulate

Assuming A1 is the cell on a Sheet where you apply a number format manually and then apply the same number format to other sheets in the workbook, place the following code on the Sheet Module.

To do so, right click the Sheet Tab --> View Code --> and paste the code given below into the opened code window.

``````Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ws As Worksheet

If Target.Address(0, 0) = "A1" Then
Cancel = True
For Each ws In ThisWorkbook.Worksheets
'Nummber format will be applied to the columns B:G on each Sheet in the Workbook
'Change it as required
ws.Columns("B:G").NumberFormat = Target.NumberFormat
Next ws
End If
End Sub``````

As per the above code, once you change the number format in A1 on the Main Sheet and double click in A1, the same number format will be applied to the columns B:G on all the sheets in the workbook.

To test the code, change the number format in A1 (yellow cell) and double click in A1 to run the code.

Related Conversations
formula for data calculation
aayushman_mishra in Excel on
10 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies