Validating user input in excel vba
As a developer myself I have seen many Excel projects that have incorporated User Forms.
Some of these look great, but certainly don't live up to their expectations. As you may now be starting to realize, formatting a User Form to look great is not that hard!
The user is asked to enter his roll number and confirm it.
Here you can have the following situations: Now how do find a solution in Excel for such problems. When we create the data input validation solution, we use the string compare function to check for a correct entry.
Full Name Set Attr myfilename, vb Read Only Active Workbook. Please contact your supervisor.” Exit Sub End If roll2 = Input Box(“please enter your roll no again”) If Str Comp(roll1, roll2) = 0 Then Range(“C1”) = roll2 ‘we call another subroutine or macro check Roll If count = 0 Then Msg Box “Roll number does not exist! Close Savechanges:=False Exit Sub End If ‘we call another subroutine or macro myfile Else counter = counter 1 Msg Box “The passwords do not match!
” & vb Cr Lf & “You have ” & 3 – counter & ” chance(s) left!
Result: Note: to remove data validation from a cell, select the cell, on the Data tab, click Data Validation, and then click Clear All.
There are many situations where we require a user to input data via an input-box.
You can use Excel's Go To Special feature to quickly select all cells with data validation.
To try and second guess the favourite color of these users would be silly.
So while you may make your User Form look great in your eyes, don't assume another user will think the same.
Also we copy some other data like a name connected to the password or roll number and then save the file.
The saved file can then be used for further evaluations.
This is a bit like those fantastic looking charts that really tell us nothing.