1

Hi my main aim is to disable entering special characters by user after he open an excel sheet using a macro in VBA

I tried how to do it. I found one method for one character.

Sub Disable_Keys()
    Dim KeysArray As Variant
    Dim Key As Variant

    KeysArray = Array("@", "!", "~")

    'Disable the StartKeyCombination key(s) with every key in the KeysArray
    For Each Key In KeysArray
        Application.OnKey Key, "myMsg"
    Next Key
End Sub

Sub myMsg()
    MsgBox "All keys are valid characters"
End Sub

There is a problem with this one i am only disable two keys in that array. If i add 3 one like "~". It's not working for that key.

If you have any other solutions also please help me.

Improve this question
0

1 Answer 1

Reset to default
1

Your code works as written.

The issue is that the OnKey method interprets "~" as the Enter key. To have the tilde actually effected use, "{~}" instead. Here's documentation on the OnKey method.

To be explicit, use this: KeysArray = Array("@", "!", "{~}").

Edit: Due to the comments, I just want to add something that you should do.

You'll want to move the code you listed to the ThisWorkBook module in the Workbook_Open event. You should also include a method such as this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Dim KeysArray As Variant
        Dim Key As Variant

        KeysArray = Array("@", "!", "{~}")

        'Disable the OnKey settings with every key in the KeysArray
        For Each Key In KeysArray
            Application.OnKey Key, ""
        Next Key
End Sub

If you do not include this method, your keys in KeysArray will attempt to run the MyMsg sub until you close the Application, even after the workbook has been closed.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Yeah it's working for some keys and not working for some keys like ^, %, (, ), +, {, },/.
I want one more help in this after i wrote my macro and save it in the excel sheet. I closed my excel sheet. Now i will type keys but it's allowing keys because i didn't run the macro. How to run a macro implicitly when i opened a macro.
Call it from the Workbook_Open event. (I assume you meant "how to run a macro when I open a workbook".)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.