Is it possible to create something similar to what is called input masks in
Access?

For example, in my Excel spreadsheet I want to be able to type a 9-digit
account number (which contains letters and numbers) into a cell and
automatically have a hyphen inserted after the first four characters. Also, I
want to be able to designate a default value (2007) that will appear in all
the cells of a column, but which the user can change to a different value
when neccessary (for example, 2006, or 2006-2007).

I think that I should be able to do this with a custom cell format, but I
have not been able to achieve this. Does anyone have any guidance to offer?
It would be MUCH appreciated!!

You can't do the formating similar to Access in Excel cells.

You can format cells as numbers or strings.

A possiible solution is to use the on_change vba macro but there are
problems with it and the coding is quite complex and you have to consider how
you deal with formula. For example i enter in C1 =A1&10*15

How do you format that.

Try this it deals with column C.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As String
On Error Resume Next
'say you are working on the column C
If Target.Column = 3 Then
On Error Resume Next
Application.EnableEvents = False
s = Target.Value
Target.Value = Left(s, 1) & "-" & Mid(s, 2, Len(s) - 1)
Application.EnableEvents = True
End If
End Sub

You can't put default values in like access because excel is sheet based and
not row based. You would have to put for versions < 12 over 65,000 rows of
default values or have a complete testing event.

What you are implying is that you want to enter data so use a form and deal
with defaults that way.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Courtney" wrote:

Is it possible to create something similar to what is called input masks in
Access?

For example, in my Excel spreadsheet I want to be able to type a 9-digit
account number (which contains letters and numbers) into a cell and
automatically have a hyphen inserted after the first four characters. Also, I
want to be able to designate a default value (2007) that will appear in all
the cells of a column, but which the user can change to a different value
when neccessary (for example, 2006, or 2006-2007).

I think that I should be able to do this with a custom cell format, but I
have not been able to achieve this. Does anyone have any guidance to offer?
It would be MUCH appreciated!!