You can create a workbook template and save it to your XLStart folder (save it
as book.xlt).
Then put everything you want new workbooks based on that template to
inherit--custom formats, page layouts, headers/footers, etc.
Then when you click on that New Icon, the new workbook will inherit all these
settings.
And numberformats work with numbers.
You could use a worksheet event that looks for changes and changes the values in
those cells to the nice format you like.
If you want to try this, rightclick on the worksheet tab that should have this
behavior. Select view code. Paste this into the code window.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myRng As Range
Dim myStr As String
Set myRng = Intersect(Target, Me.Range("a:a"))
If myRng Is Nothing Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
For Each myCell In myRng.Cells
With myCell
If IsNumeric(.Value) Then
.NumberFormat = "00-00-00"
ElseIf Len(.Value) = 6 Then
.Value = Left(.Value, 2) & "-" & _
Mid(.Value, 3, 2) & "-" & _
Right(.Value, 2)
End If
End With
Next myCell
Application.EnableEvents = True
On Error GoTo 0
End Sub
Change this line to just the range that you want "formatted":
Set myRng = Intersect(Target, Me.Range("a:a"))
(I used all of column A.)
And if you typed in a number, I changed the format. If you typed in text, I
changed the value.
