Prevent from entering certain characters

[sapka]

I want to prevent user from entering certain characters like "+", ",", "&" etc. I could use Data Validating tool or use Private Sub Worksheet_Change. However, validation in both cases is triggered only after user presses enter. I want it to work like KeyPress event for userform controls - user types a character, macro checks it, if it is not allowed, character is not displayed on the screen.

 

[Gary McMaster]

Please try these samples:

I hope it is what you were looking for.

Gary

Public Sub DisableKey()
 
'Run once to activate key trapping probably best in Workbook Open or Sheet Activate etc.
Application.OnKey "&", "ClearKey"' Call ClearKey procedure & erase keystroke
Application.OnKey "{+}", "ClearKey"
Application.OnKey ",", "ClearKey"
 
End Sub

Public Sub ResetKey()
 
'Restore key functionality
Application.OnKey "&"
Application.OnKey "{+}"
Application.OnKey ","
 
End Sub

Public Sub ClearKey()
 
Application.ActiveCell.Value = ""
 
End Sub

 

[sapka]

Thanks! It works.