Hi all,
I've found threads that discuss adding zeroes to create a fixed amount of digits but I'm interested in something different.
First I have this:
A1= 123
A2= 456
A3= 789
Then changing the variable AddZeroes to 5 leads to:
A1= 00000123
A2= 00000456
A3= 00000789
How to do this with VBA?
Help with this would be much appreciated
Custom number formatting = 00000#####
Elegant Simplicity............. Not Always
Thanks! How can I apply that in VBA?
In the worksheet code module
Private Sub Worksheet_Change(ByVal Target As Range) If IsNumeric(Target.Value) Then Target.NumberFormat = "00000#####" End Sub
Last edited by AndyLitch; 01-31-2014 at 05:33 AM.
I see the entire sheet is now affected. Is it possible to limit the effect to a specified column?
Great, you solved it
Think I found an application for a specific column. With the below code it only works if the format of the cells is "Text". How do I get it to work if it is "General"?
Dim i As Integer For i = 1 To 4 Cells(i, 1).Value = Format(Cells(i, 1).Value, "00000####") Next i
The following addition seems to help
Worksheets("Sheet1").Range("A1:A4").NumberFormat = "@"
If you only want to apply it to a specific column (Say D for arguments sake) then
or you could use :-Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column <> 4 Then Exit Sub If IsNumeric(Target.Value) Then Target.NumberFormat = "00000#####" End Sub
And you can restrict the rows with the Target.Row property in the same wayIf Left(Target.Address, 2) <> "$D" Then Exit Sub
Last edited by AndyLitch; 01-31-2014 at 06:25 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks