September 26, 2014

How to capture user input via input box and validate the data input automatically using Excel VBA

There are many situations where we require a user to input data via an input-box. Now how do we check and confirm that the user input is correct and valid. We could have a situation where a user enters a roll number to participate in an exam. The user is asked to enter his roll number and confirm it. Here you can have the following situations:

  1. The user does not enter the correct roll number. Here we need to warn the user and give another opportunity to enter the roll number or password again
  2. The user enters a roll number that does not exist at all. Here also we need to warn the user that he has made an incorrect data entry
  3. The user enters the correct password and also confirms it. Now we need to compare the entered roll number or password with an existing list. This completes the validation of the user input via an input-box

Now how do find a solution in Excel for such problems. We use Excel VBA.

When we create the data input validation solution, we use the string compare function to check for a correct entry. Once the correct data entry is confirmed, we use a looping process to compare the entered data with an existing list. When we find a correct input we validate it by counting an entry. 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. You can now check a student’s exam paper or study how many times during the day an authorized user viewed certain data.

View the video carefully before you study the complete VBA code given below:

View this video on YouTube.

Public roll2 As String
Public count As Integer

Function IsWorkBookOpen(FileName As String)

Dim FF As Integer, ErrNum As Integer

‘We turn off error checking
On Error Resume Next
‘The inbuilt function gets a free file number.
FF = FreeFile()
‘we try to open the file and lock it

Open FileName For Input Lock Read As #FF
‘Close the file
Close FF
‘capture the error number
ErrNum = Error
‘Turn on error checking
On Error GoTo 0
‘Find which error happened
Select Case ErrNum
‘No error
‘File is not open
Case 0: IsWorkBookOpen = False
‘File already opened by another user
Case 70: IsWorkBookOpen = True
‘Some other error occurred. Capture the error number for further action
Case Else: Error ErrNum
End Select
End Function

Sub checkRoll()
Dim info
info = IsWorkBookOpen(“C:\rollnumbers\rollnumbers.xlsx”)
‘we open the workbook if it is closed
If info = False Then
Workbooks.Open FileName:=”C:\rollnumbers\rollnumbers.xlsx”
End If
Dim i As Long, lastrow As Long
lastrow = Sheets(“Sheet1”).Cells(Rows.count, 1).End(xlUp).Row
‘Dim count As Integer
count = 0
For i = 2 To lastrow
If roll2 = Cells(i, 3) Then
count = count + 1
Cells(i, 1).Copy
End If
Next i
ActiveWorkbook.Close Savechanges:=False

End Sub

Sub myfile()
Dim Path As String
Dim name1 As String
Dim myfilename As String
Dim mydate As String

Path = “C:\ExcelExams\”
ActiveSheet.Paste Destination:=Cells(1, 4)
name1 = Range(“C1”).Value
mydate = Date
mydate = Format(mydate, “mm_dd_yyyy”)
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=Path & name1 & “-” & mydate & “.xlsx”, FileFormat:=51
myfilename = ActiveWorkbook.FullName
SetAttr myfilename, vbReadOnly
Application.DisplayAlerts = True

End Sub

Private Sub Workbook_Open()

counter = 0
Dim roll1 As String
‘Dim roll2 As String

roll1 = InputBox(“Please enter your roll number”)
If roll1 = “” Then
MsgBox “You entered no roll number or clicked on cancel. Please contact your supervisor.”
Exit Sub
End If
roll2 = InputBox(“please enter your roll no again”)
If StrComp(roll1, roll2) = 0 Then
Range(“C1”) = roll2

‘we call another subroutine or macro


If count = 0 Then
MsgBox “Roll number does not exist!”
ActiveWorkbook.Close Savechanges:=False

Exit Sub
End If

‘we call another subroutine or macro


counter = counter + 1
MsgBox “The passwords do not match!” & vbCrLf & “You have ” & 3 – counter & ” chance(s) left!”

If counter >= 3 Then
MsgBox “Please contact your supervisor!”
Exit Sub
End If
GoTo TryAgain
End If
End Sub

Further reading:

Timer in Excel Using VBA

Test If a File Is Open

For Loop in Excel VBA

Download a sample file by clicking on the Excel icon:

2 thoughts on “How to capture user input via input box and validate the data input automatically using Excel VBA

  1. Hi
    Just want to say that I am learning a lot by watching you videos and also scratching my head due to syntax’s errors. Not sure if you have or can create a user input activity timer. What I mean by that is that I am looking for anyone to go to the excel sheet and choose an activity and the date and time start to calculate through that process. along with that said, I am also looking to have it do that once that first process is complete they would hit stop and choose the next step and have he timer calculate that time. This is more of a activity timer collect the time on each step through the process and have it save to a file so I can covert that to a chart. Please help

Comments are closed.