custom format & saving
 

Is there a way to save this format type so I can use it in another workbook?
The custom format only stays in the workbook it was created in.

Also, how do I create a format for something like 12-34-5F or 12-34-56?
00-00-00 works if all digits but won't work in the first example. @@-@@-@@
just repeats everything nor does @-@-@ work.

Thanks!

Unfortunately, I think the best way for you to do this is to enter it as
text. (put a hyphen before your entry)

"kdw" wrote:

Is there a way to save this format type so I can use it in another workbook?
The custom format only stays in the workbook it was created in.

Also, how do I create a format for something like 12-34-5F or 12-34-56?
00-00-00 works if all digits but won't work in the first example. @@-@@-@@
just repeats everything nor does @-@-@ work.

Thanks!

I bet bj meant the apostrophe ('), not the hyphen (-).



bj wrote:

Unfortunately, I think the best way for you to do this is to enter it as
text. (put a hyphen before your entry)

"kdw" wrote:

Is there a way to save this format type so I can use it in another workbook?
The custom format only stays in the workbook it was created in.

Also, how do I create a format for something like 12-34-5F or 12-34-56?
00-00-00 works if all digits but won't work in the first example. @@-@@-@@
just repeats everything nor does @-@-@ work.

Thanks!

--

Dave Peterson

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.

kdw wrote:

Is there a way to save this format type so I can use it in another workbook?
The custom format only stays in the workbook it was created in.

Also, how do I create a format for something like 12-34-5F or 12-34-56?
00-00-00 works if all digits but won't work in the first example. @@-@@-@@
just repeats everything nor does @-@-@ work.

Thanks!

--

Dave Peterson

senior moment

"Dave Peterson" wrote:

I bet bj meant the apostrophe ('), not the hyphen (-).



bj wrote:

Unfortunately, I think the best way for you to do this is to enter it as
text. (put a hyphen before your entry)

"kdw" wrote:

Is there a way to save this format type so I can use it in another workbook?
The custom format only stays in the workbook it was created in.

Also, how do I create a format for something like 12-34-5F or 12-34-56?
00-00-00 works if all digits but won't work in the first example. @@-@@-@@
just repeats everything nor does @-@-@ work.

Thanks!

--

Dave Peterson

Dave, I tried your approach of using the Change Event and it worked, although
it isn't quite what I was looking for originally. I'll have to keep this
piece of code in my 'folder' for future reference.

I gather that the short answer to my question is that we can't create Custom
Format for non-numbers ( at least not in the Format Cell menu). I wonder if
this has changed in the newer version of Excel?

"Dave Peterson" wrote:

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.

kdw wrote:

Is there a way to save this format type so I can use it in another workbook?
The custom format only stays in the workbook it was created in.

Also, how do I create a format for something like 12-34-5F or 12-34-56?
00-00-00 works if all digits but won't work in the first example. @@-@@-@@
just repeats everything nor does @-@-@ work.

Thanks!

--

Dave Peterson

Yep. When excel says NumberFormat, it means number format <vbg.



kdw wrote:

Dave, I tried your approach of using the Change Event and it worked, although
it isn't quite what I was looking for originally. I'll have to keep this
piece of code in my 'folder' for future reference.

I gather that the short answer to my question is that we can't create Custom
Format for non-numbers ( at least not in the Format Cell menu). I wonder if
this has changed in the newer version of Excel?

"Dave Peterson" wrote:

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.

kdw wrote:

Is there a way to save this format type so I can use it in another workbook?
The custom format only stays in the workbook it was created in.

Also, how do I create a format for something like 12-34-5F or 12-34-56?
00-00-00 works if all digits but won't work in the first example. @@-@@-@@
just repeats everything nor does @-@-@ work.

Thanks!

--

Dave Peterson


--

Dave Peterson