Oct 02 2019 07:08 AM
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.
Oct 02 2019 08:44 AM
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.
Oct 02 2019 10:07 AM
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.