onkey help

[wardy]

I'm trying to disable the click of the comma button on a particular
worksheet in my excel workbook. Using the application.onkey feature I
have been able to disable this when a user single clicks on a
particular cell and tries to click the comma button.

The problem is, if a user double-clicks on a particular cell, or types
an accepted character in a cell, then the block on the comma no longer
applies and the user can enter the invalid character.

Any ideas how to get around this problem?

Thanks

 

[Dave Peterson]

When you're in edit mode, any useful macro can't run.

Maybe you could just eliminate the comma when you're doing the processing--or
just catch it when the user types it in with a worksheet event:

Right click on the worksheet tab and select view code. Paste this in the code
window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range

On Error GoTo errHandler:

Application.EnableEvents = False
For Each myCell In Target.Cells
If myCell.HasFormula Then
'do nothing
Else
If InStr(1, myCell.Value, ",") > 0 Then
myCell.Value = Application.Substitute(myCell.Value, ",", "")
End If
End If
Next myCell

errHandler:
Application.EnableEvents = True
End Sub

This changes values--not formats, so 123,456 (as a number) wouldn't change.

 

[David Sauder]

You can block comma entries using the data validation function.
Select the range of cells you want to block commas from, then choose
<data><validation>. In the "Allow" box, pick "custom". In the
formula box enter this

=ISERR(FIND(",",a1,1))

(Substitute the current active cell address for a1)

The FIND function looks for a comma in any entry. If it doesn't find
one it returns an error value which the ISERR value converts to TRUE -
and data validation allows the entry. If there IS a comma, the FIND
function does not return an error value, so ISERR return FALSE, and
the entry is blocked. You might also want to enter a message on the
"error alert" tab of the data validation form, to let the user know
why their entry is being blocked.

Once you get this working, you can ditch the ONKEY approach - besides
not working in every circumstance, it can slow down your application
when that code runs with every keystroke.

David S.