Prevent certain characters being entered into a text box

[RitaMoloney]

Hi,

I have a form that is used to enter employee information. On the text box for entering employee name I want to prevent the user from entering certain characters such as ' . If the user enters an employee's name like Barry O'Connor a message should appears saying

Please do not use the following characters when entering a name: ' .

At the moment this works if you only enter ' in the text box but if you enter text with it i.e. Barry O'Connor , it does not work.


This is the code I have entered in the before update event of EmployeeName text box.

Private Sub EmployeeName_BeforeUpdate(Cancel As Integer)
If Me.EmployeeName.Value = " ' " Then
MsgBox " Please do not use following characters when entering a name; ' ", vbExclamation
Me.EmployeeName.Undo
Cancel = True
End If
End Sub

Has anyone any idea how to solve this problem.

Thanks in advance for any help.

 

[Mile-O]

Why do you want to disallow the ' ?

If it's because you have something like this:

"[Surname] = '" & [Enter name] & "'"

Then simply change it to this:

"[Surname] = """ & [Enter name] & """"

Simply said, within a string two "" evaluates as one " and is far more robust than using ' as a delimiter.

 

[DrSnuggles]

Reply

Hi there,

This module wil delete any entries by the user that you determine.

Please rename all the 'txtbox' to the name of your textbox on your form.
the KeyCode 192 is the ASCII value for " ' " .
For anymore just add them after the no. 192. I have added the letter A (65, for example).

Look up the ASCII values for other key strokes that you do not want.

Private Sub txtBox_KeyUP(KeyCode As Integer, Shift As Integer)
' ++++++++++++++++++++++++++++++++++++++++++++++++
' Purpose : To prevent user's from entering predefined keys
' Created By :
' Date : 14/09/2004
' ++++++++++++++++++++++++++++++++++++++++++++++++

Select Case KeyCode

Case 192, 65
txtBox.Text = Left(txtBox.Text, Len(txtBox.Text) - 1)
txtBox.SetFocus
txtBox.SelStart = Len(txtBox.Text)
txtBox.SelLength = 0
Exit Sub
End Select

End Sub


Good luck!

 

[RitaMoloney]

Hi,

Thanks for that, it worked perfectly. I added a message box to make it more user friendly.


Case 192, 65
MsgBox "Please do not use the following characters when entering a name: ' A", vbInformation, "Information..."
EmployeeName.Text = Left(EmployeeName.Text, Len(EmployeeName.Text) - 1)
EmployeeName.SetFocus
EmployeeName.SelStart = Len(EmployeeName.Text)
EmployeeName.SelLength = 0
Exit Sub
End Select


The reason I need this is because on a different form I use a list of employee names. If there is a " ' " in a employee name, the vb code picks it up as part of the code and produces an error. So, I figured the easiest way around this would be to prevent the user from entering the '.


Well, thanks again.
Rita

 

[Mile-O]

The reason I need this is because on a different form I use a list of employee names. If there is a " ' " in a employee name, the vb code picks it up as part of the code and produces an error.

So, I figured the easiest way around this would be to prevent the user from entering the '.

You didn't read what I said, did you? That's the easiest and most logical - just change each ' to "" and you can let your users enter any name they want including an '

 

[RitaMoloney]

Hi,

Sorry, it too so long to get back to you, I was out of the office.

Err!!! You were right, I tried out your suggestion and it works.
I made an attempted at it the other day when you first posted it but I obviously did something wrong as it didn’t work for me. Anyway, I tried it again and it works.

Thanks again to both of you for all your help and patiences.

Rita

 

[ecniv]

Just another option..

VBA replace function and replace the single quote (') with the apostrophy (`)


Vince

 

[Mile-O]

Why use workarounds when there's a simple solution?