How to allow only input of numerical values in userform text-box using Excel VBA
HTML-код
- Опубликовано: 7 апр 2013
- How can we prevent users to avoid errors while entering data into a User Form in MS Excel? Often we need to enter only numerical values for the price of an item in a text-box on a user-form. We can achieve this by using Excel VBA.
Since the data entry happens on 'key press' we use this feature to capture any invalid entries like alpha values. Each key-stroke or key-press is assigned a numerical value in the American standard Code for Information Interchange or ASCII. Wikipedia describes ASCII as: The American Standard Code for Information Interchange (ASCII) is a character-encoding scheme originally based on the English alphabet. ASCII codes represent text in computers, communications equipment, and other devices that use text. Most modern character-encoding schemes are based on ASCII, though they support many additional characters.
Using the above information we can write our code that allows users to only enter numerical data into a text-box on a user-form.
Thank you. Exactly what I needed
Thanks a lot MR. Dinesh, your video is very clear and usefull, I use the code and it work perfectly, my Project work much betther now.
Thank you very much sir, for this valuable lesson.
Thank you very much sir, I have used same code for one of my userform..its working...Thank you for sharing
Thats simple and perfect code. Sir, you are exceptionally skilled. I have been looking for this since a week. And here we go... Thank You Sir.
I guess Im asking the wrong place but does any of you know a method to get back into an instagram account..?
I somehow lost the login password. I love any tricks you can give me!
@Malcolm Raphael instablaster ;)
@Moshe Travis thanks so much for your reply. I got to the site through google and im trying it out now.
Takes quite some time so I will get back to you later when my account password hopefully is recovered.
@Moshe Travis it worked and I finally got access to my account again. Im so happy:D
Thank you so much you saved my ass !
@Malcolm Raphael No problem xD
Thank You So much Sirji See this Video After Slove my Problem
Thank you. This was helpful.
Thank you for this material. It was exactly the thing I was looking for :)
Glad it was helpful! Please share the Excel VBA learning tutorial with your friends too.
Very easy to follow. Thanks
very helpful. many thanks
Thank you so much for the help. More power!
Glad it helped!
I have a similar video on this using option buttons. For example, when you select T1 you can make the relevant TextBoxes visible to the data entry operator. When he selects T2 then only the other TextBoxes are visible.
Thanks so much!
Thanks a lot. Works perfectly.
Glad it helped! Please share the VBA tutorials with your friends.
@@Exceltrainingvideos I will!!
Amazing!
that solve my problem, thx
Great Video
Wow thankyou
sir big fan of ur videos..plz guide me on following queries of you can:-
1. how to restrict a userform textbox to generate automatic numbers from 1 but upto any 6 digits numbers and one any alphabet subject to requiremnt. This Alphabet may be manually added in textbox. but the textbox shall not accepts two alphabets. eg.123456A, 123456B etc.
2. Make a video on login Id where Admin can assign role to other users and to recored login and log out time.
Thank you
Welcome!
Hello, thanks for sharing this excellent tutorial video.
Please guide me:
Using a textbox having format "#0.00"
i want that user does not need to press "." and pointer automatically passes to first digit after decimal AFTER filling the positions before decimal.
Use LEN to check the length entered and then use CONCATENATE - my immediate thoughts.
very good and a lot of thanks
but how i can restrict the user to not increase or decrease the numbers more or less than 10
THANKS SIR
Most welcome
Thanks very much. It's very interesting and useful for me. But what about textbox format for "hour" and " minutes” ?
Just enter the hours and minutes with am or pm in the text box and transfer the data to a cell on the worksheet, if that's your goal. Example: 12:30 AM
Hi! I saw your video using option button. I have a query on this. how the combo box can select option button? What I mean is that if I click the combobox I can select different the option button. Thanks.
Hi, need some help from you. may i know how to format textbox in a userform ? i just want it to be in 2 decimal places.
The value from this textbox is from the value of ( textbox1 / textbox2 )
Can please help ?
By the way this textbox don't link to any of the worksheet.
Thanks !
Thanks. I find your videos very helpful.
I used the code here to try to limit the data input in my textbox to numeric values to overcome a problem I am having. When I submit the data from the userform to the assigned cells in the spreadsheet, the numeric value appears correctly however it is "left-justified" within the cell, despite my designating the cells in that column as "number formatted". Can you think of why this happens?
Jeff Pike Data is being transferred from a 'TextBox' so the numerical values are being treated as text. This link might help: www.exceltrainingvideos.com/user-form-for-calculations-in-excel/
Dinesh Kumar Takyar Thanks for your prompt response! Was not familiar with the Val function. It beats "Copy, Paste Special, Multiply" for simplification.
Hello sir nice tutor video, i have some question on KeyAscii code, if I have numerous of textbox how do I apply it on all my textbox with single code (applying same code to multiple textboxes) or in specific textbox? Hope you can help me, thanks in advance.
Iskandar Tony Jovini
I got Same Problem. If u know now can u please help me.
Thanks
Hi Dinesh, this is really good! I am encountering an error after entering numeric values in the textbox, I used the backspace, no issues until the last digit, I get Run-time error "13", a type mismatch.
You can find the complete code here: www.exceltrainingvideos.com/how-to-allow-only-numerical-values-in-excel-user-form-text-box-using-vba/
You may also like to serach at exceltrainingvideos.com
Hi! How to make data entry by user form that you can entry in particular txtbox, C, D,E, F, G, H, I. For example in the drop down menu, if I will select T1, then only C, D,E, F txtbox can make an entry and all the other txtbox could not allow to make any entry. And then, If I select T2 in the drop down menu, then D, F, H, I txtbox can make an entry all other txt box are freeze.
Also, how can I do this in excel. Thanks.
Mr. Dinesh Kumar
How if I wont the user to enter only 9 digits, ( 9 integers ) not allowing him to enter string or Dot's or anything else.
Thanks again
This link will guide: www.exceltrainingvideos.com/limit-user-input-in-a-text-box-or-an-excel-worksheet-cell/
Or serach www.exceltrainingvideos.com
Thanks very much for your help Mr.Dinesh and for your quick response
Mr.Denish
I have two drop-down list
The first drop list content the area
The second drop list content the sales man
What I want to do is , when I choose the area from the first drop list in the second drop list I want the names of sales man for the selected area only
Kindly helpe me
Hi!
Thanks for the video, but isn't "KeyAscii=KeyAscii" very unnecessary? I would do it like this:
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not ((KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 44) Then
KeyAscii = 0
End If
End Sub
First, allowing spaces in numerical values doesn't make sense, at least not to me.
Second, why the message box? Without it, there is no annoying message box and you are still prevented to input other things than numbers and (in my case) a decimal comma (46).
Third, always indent your code properly. It makes it a lot easier to follow.
Totally agreed with you, I was about to write that comment and just saw your's.
But here you can shorten If statement like that:
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not ((KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 44) Then
KeyAscii = 0
End Sub
Hi how are you, this video is very helpful but I'm facing a problem when the user enters 1000 the error dialog will appear. I would love to restrict my text box input to be greater than 0 and a numerical value. if the user enters a string or zero or less than zero the error dialog will appear. But keep in mind that decimal point (e.g.0.02) should be accepted as input.
Search www.exceltrainingvideos.com
Dear Sir, Is there any code for activating the CAPSLOCK key on got focus or lost focus events in Excel VBA, If so kindly make a video on it
You can create the following two modules to activate and deactivate CAPSLOCK automatically:
Declare Function GetKeyboardState Lib "user32" (pbKeyState As Byte) As Long
Declare Function SetKeyboardState Lib "user32" (lppbKeyState As Byte) As Long
Sub SetCapLockON()
Dim Res As Long, KBState(0 To 255) As Byte
Res = GetKeyboardState(KBState(0))
KBState(&H14) = 1
Res = SetKeyboardState(KBState(0))
End Sub
Declare Function GetKeyboardState Lib "user32" (pbKeyState As Byte) As Long
Declare Function SetKeyboardState Lib "user32" (lppbKeyState As Byte) As Long
Sub SetCapLockOFF()
Dim Res As Long, KBState(0 To 255) As Byte
Res = GetKeyboardState(KBState(0))
KBState(&H14) = 0
Res = SetKeyboardState(KBState(0))
End Sub
Thank you but it accepts no limit points the ACSII 46. for exemple .....2645.252.....25
How to restrict only one point?
Thank you
Interesting question:
Select Case KeyAscii
Case 48 To 59
' Allow digits
Case 46
' Allow only one dot
If InStr(TextBox2.Text, ".") Then KeyAscii = 0
Case Else
' Block any invalid entry
KeyAscii = 0
End Select
Dinesh Kumar Takyar It works thank you
How to use thai language in 1 textbox and last in english thanks
How can I use a textbox to enter time in 24:00 format with error alart in case of >24:00.
This Excel VBA tutorial will guide: www.exceltrainingvideos.com/custom-function-to-check-time-entries/
how can use this code in modules or multiple text box use this one code
Thank you very much sir Excel VBA input/text box tutorial. In addition to limiting this how We can allow text box to accept Exponential and Multiplication symbol .For Example values like 10^6 or 89.45e+6.Plz Guide .......Thanks once again
This VBA tutorial will guide: www.exceltrainingvideos.com/how-to-add-numerical-values-via-text-boxes-in-userform/
Mr. Dinesh can you give me an explanation regarding my texbox1 that would have a limit.
Here is it:
supposed i have the limit up to 29 and my vba like this:
If me.textbox1 > 29 then
msgbox("Your number exceed the limit")
exit sub
end if
BUT THE PROBLEM Even i have to input 3 up to 9 the result still "YOUR NUMBER EXCEED THE LIMIT" no proble for 11 up to 29.
Is there any solution regarding this matter?
How to rewrite into textbox1 without clearing the data?
since this textbox1 i am using TEXTBOX1_CHANGE() because i need to calculate by itself.
I have notice is like single digit only, remember 29 the first digit is 2 now when you input 3 it will be automatically belong to greater than 29.
Do you have any solution for this?
Kindly just give me your thought. Thanks in advance
Use val(me.textbox1) > 29
Hello sir,
your are vba videos are very useful. but one thing i am facing one problem of in vba data entry user form. I have created data entry form, but i want to protect worksheet without data entry user form. I have tried to use your videos for the problem but did not success. while i will data entry in user form that time worksheet will be protect automatically. please solve this problem.
Search www.exceltrainingvideos.com/
thanks a lot sir, it's really works well. I would like to add some more features in my form, putting the mark "-" between numbers, I want 123456789011 in this shape 12-34-5678911. and one more thing I want them limited no more no less than 11 digits. how is this possible
These VBA tutorials will help:
www.exceltrainingvideos.com/tag/validate-alphanumeric-data-in-text-box-on-user-form/
www.exceltrainingvideos.com/formatting-data-in-excel-using-vba/
www.exceltrainingvideos.com/format-data-for-user-friendly-presentation/
For more help search www.exceltrainingvideos.com
How I can prevent copy paste data from textbox in Userform. Please guide me
Good question. I'll make a video on this topic soon which can be useful for all learners.
i want to learn about DATE format
Search www.exceltrainingvideos.com
how to verifiy personal PAN in vba
You can verify any entry including PAN by comparing the already entered PAN on worksheet with an entry, let's say, via an inputbox.
Search www.exceltrainingvideos.com
thanks sir
one major problem is if you press ctrl+Tab when you data enter or Edit Number is move in same box or space before or after number
it means entered value is not numeric value in excel sheet
Think again. What is the aim?
sir help textbox range rwo send out data
exp.....
text box tyep
>>>>>>>
dear sir { xyznamecall } your demo
ye kese set kare { xyznamecall } calle name
Excel is cool ........Isn't It?
I said ..............isn't it?
+Brandon LOL...
What if, if you give the project of your file..
Bloody Dam, chahca tou ne waqt zaya kr dia,,,
abki baar koi video banana tou sath me oski file google drive main rakhna.
wrna dfa hoja youtube se, cheap insaan