Input Masks or equivilent

[Courtney]

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!!

 

[Martin Fishlock]

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.