 • 411K Members
• 5,186 Online
• 466K Conversations

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
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies