I am writing an Excel app in which the user needs to enter dashes
frequently ('-'). However, Excel thinks the user is trying to enter a
function, and if the user presses the down arrow, Excel inserts a
reference to that cell instead of moving down to that cell. Is there
any way to tell Excel not to expect a function? Setting the cell's
format to text doesn't work, and the Worksheet_Change event doesn't
fire until the user is finished editing the cell. Maybe there is some
property in the Application object I can set to fix this?

TIA,

- Bill

Bill,

Application.HyperactiveBehavior = False

Well, we can wish, can't we? This is one of Excel's hyperactive tricks. No
one, except a couple of programmers at Microsoft know why this is there.
Like automatic date formatting, it confounds more people than it helps.
It's actually a formula that Excel presumes is desired. Yet it appears to
be a bug, because if the cell is preformatted for text, it puts the cell
reference, as you said, but doesn't make it a formula (doesn't put = in
front of it as it does in a non-text-formatted cell).

Presumably the user wants only the - character in the cell, and wants to go
directly to another cell via an arrow key once it's been typed. After
typing the -, the user can press Enter, then use the arrow keys. Or precede
the - with an apostrophe ('). Or press F2 (Edit mode instead of Enter mode)
before typing the -. That's about the best I know of.
--
Earl Kiosterud
www.smokeylake.com

"Bill P" wrote in message
ups.com...
I am writing an Excel app in which the user needs to enter dashes
frequently ('-'). However, Excel thinks the user is trying to enter a
function, and if the user presses the down arrow, Excel inserts a
reference to that cell instead of moving down to that cell. Is there
any way to tell Excel not to expect a function? Setting the cell's
format to text doesn't work, and the Worksheet_Change event doesn't
fire until the user is finished editing the cell. Maybe there is some
property in the Application object I can set to fix this?

TIA,

- Bill

Thanks for the help. I've developed a workaround that supresses the
function error message, but does not produce the ideal behavior.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True

If Strings.Left(Target.Value, 1) = "-" Then
Target.Value = "-"
Exit Sub
End If

Application.EnableEvents = False

' Rest of validation...
End Sub

It sounds like it may not be possible to prevent Excel from treating
that as a formula. Oh well...

- Bill

Hi Bill,

You might want to swap those EnableEvents around: False first, then True
- and insert them inside the IF/End IF. (I'm sure you know that.)

You could instruct your users to insert a space before the dash - if you
think they'll find it easier. Then you could clean it up with something
like:

If Left$(Target.Value, 1) = " " Then
Application.EnableEvents = False
Target.Value = LTrim(Target.Value)
'optionally...
Target.NumberFormat = "@"
Application.EnableEvents = True
End If

Personally, I'd train them to use the apostrophe as Earl suggested if
you can though. It's quick and useful for them to know in general Excel use.

Q: Why do they need to enter dashes anyway? Is it just to indicate
they've left that cell blank of something? In which case you may be
better off having them insert a space and changing it through code using
something like:

If Target.Value = " " Or Target.Value = " " Then
Application.EnableEvents = False
Target.Value = "'-"
Application.EnableEvents = True
End If

cya,
G

Bill P wrote:
Thanks for the help. I've developed a workaround that supresses the
function error message, but does not produce the ideal behavior.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True

If Strings.Left(Target.Value, 1) = "-" Then
Target.Value = "-"
Exit Sub
End If

Application.EnableEvents = False

' Rest of validation...
End Sub

It sounds like it may not be possible to prevent Excel from treating
that as a formula. Oh well...

- Bill