Home

Decimal Point Formula - Autopopulate

%3CLINGO-SUB%20id%3D%22lingo-sub-888855%22%20slang%3D%22en-US%22%3EDecimal%20Point%20Formula%20-%20Autopopulate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888855%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20formula%20or%20something%20in%20VBA%20that%20will%20help%20me%20do%20mass%20changes%20to%20the%20amount%20of%20decimal%20points%20allowed%20in%20a%20cell.%20I'd%20like%20to%20be%20able%20to%20set%20the%20amount%20of%20decimal%20points%20in%20one%20cell%2C%20and%20then%20it%20will%20change%20it%20across%20all%20of%20my%20sheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-888855%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889166%22%20slang%3D%22en-US%22%3ERe%3A%20Decimal%20Point%20Formula%20-%20Autopopulate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889166%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418791%22%20target%3D%22_blank%22%3E%40krbuck0%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20an%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20Dosomething()%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3BDim%20xSh%20As%20Worksheet%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3BApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3BFor%20Each%20xSh%20In%20Worksheets%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3BxSh.Select%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3BCall%20RunCode%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3BNext%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3BApplication.ScreenUpdating%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20RunCode()%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3BRange(%22%3CFONT%20color%3D%22%23FF0000%22%3EA1%3AA5%3C%2FFONT%3E%22).Select%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3BSelection.NumberFormat%20%3D%20%22%3CFONT%20color%3D%22%23FF0000%22%3E0.00000%3C%2FFONT%3E%22%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20change%20the%20text%20in%26nbsp%3B%3CFONT%20color%3D%22%23FF0000%22%3Ered%3CFONT%20color%3D%22%23000000%22%3E%20to%20fit%20your%20specific%20range%20and%20number%20format.%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889323%22%20slang%3D%22en-US%22%3ERe%3A%20Decimal%20Point%20Formula%20-%20Autopopulate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889323%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418791%22%20target%3D%22_blank%22%3E%40krbuck0%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20A1%20is%20the%20cell%20on%20a%20Sheet%20where%20you%20apply%20a%20number%20format%20manually%20and%20then%20apply%20the%20same%20number%20format%20to%20other%20sheets%20in%20the%20workbook%2C%20place%20the%20following%20code%20on%20the%20Sheet%20Module.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20do%20so%2C%20right%20click%20the%20Sheet%20Tab%20--%26gt%3B%20View%20Code%20--%26gt%3B%20and%20paste%20the%20code%20given%20below%20into%20the%20opened%20code%20window.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_BeforeDoubleClick(ByVal%20Target%20As%20Range%2C%20Cancel%20As%20Boolean)%0ADim%20ws%20As%20Worksheet%0A%0AIf%20Target.Address(0%2C%200)%20%3D%20%22A1%22%20Then%0A%20%20%20%20Cancel%20%3D%20True%0A%20%20%20%20For%20Each%20ws%20In%20ThisWorkbook.Worksheets%0A%20%20%20%20%20%20%20%20'Nummber%20format%20will%20be%20applied%20to%20the%20columns%20B%3AG%20on%20each%20Sheet%20in%20the%20Workbook%0A%20%20%20%20%20%20%20%20'Change%20it%20as%20required%0A%20%20%20%20%20%20%20%20ws.Columns(%22B%3AG%22).NumberFormat%20%3D%20Target.NumberFormat%0A%20%20%20%20Next%20ws%0AEnd%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20per%20the%20above%20code%2C%20once%20you%20change%20the%20number%20format%20in%20A1%20on%20the%20Main%20Sheet%20and%20double%20click%20in%20A1%2C%20the%20same%20number%20format%20will%20be%20applied%20to%20the%20columns%20B%3AG%20on%20all%20the%20sheets%20in%20the%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20test%20the%20code%2C%20change%20the%20number%20format%20in%20A1%20(yellow%20cell)%20and%20double%20click%20in%20A1%20to%20run%20the%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
krbuck0
Occasional Visitor

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

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.

@krbuck0 

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
Need FORMULA help please!!
marislav in Excel on
4 Replies