Free MS-Excel Training Videos Home

How to ensure that all data is entered properly into specific worksheet cells in Excel


So you have protected the worksheet and unlocked only the spreadsheet cells into which data will be entered. You have also provided proper headers against the unlocked cells. The user still forgets to enter all the data. What can we do? Well, we can warn the data entry operator if she misses her entry and also guide her about which data she has forgotten so that she can take corrective action. Since we are in the process of creating a macro using VBA we might as well introduce some automation so that the job becomes even easier. The code that we wrote is given below:


Sub calculate()
'
' calculate Macro
' ' Keyboard Shortcut: Ctrl+t
'
If Cells(3, 3) = "" Then
MsgBox "Please enter the item description"
End If
' Here we introduce an icon also in the warning
If Cells(4, 3) = "" Then
MsgBox "Please enter the Unit Price", vbCritical
End If
If Cells(5, 3) = "" Then
MsgBox "Please enter the Quantity", vbCritical
End If
Cells(6, 3) = Cells(4, 3) * Cells(5, 3)
'Tax is assumed to be 5%
Cells(7, 3) = Cells(6, 3) * 0.05
Cells(8, 3) = Cells(7, 3) + Cells(6, 3)
End Sub

Watch the training video (about 25 MB) to understand the solution thoroughly!








Ensuring Data Entry into Specific Excel Worksheet Cells