Userforms can be very useful and powerful when it comes to creating Data entry forms or Databases in Excel. One of the most important aspects of a database is to have clean data, that is well structured. Using this VBA example can limit the entries that a user can make in entering data into specific sections or textboxes. It may be a telephone number that must contain only digits and no brackets or special characters, it may be a name that must have no spaces. Whatever you need to allow or restrict in the entering of data, it’s possible in userforms.
This particular example covers all the possibilities of allowing / disallowing numbers, alpha characters, spaces, and deleting. You can comment and uncomment out the relevant lines of code to obtain your necessary rules. It is set for the control named TextBox15 but can be adapted to other fields you need.
This code is used in most datanology database projects and in our data entry form designs. Contact us using the form or leave a message for assistance with your Excel project.
Private Sub TextBox15_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57 'integer/whole number
Case 65 To 90, 97 To 122 'Alpha lower and UPPER case
Case 32 'Space
Case vbKeyBack 'Backspace/delete
KeyAscii = 0