How to create a super easy secure data entry form using VBA. We demonstrate how to use data validation to enter data in worksheet cells using dynamic tables. Using two functions, we create two macros to transfer the data from the form to the data worksheet. The tables are created in a third sheet called ‘ItemDetails’. We make the ItemDetails worksheet very hidden to allow only users with authority to change values in the tables. We protect the ‘form’ worksheet in a such a manner that only specific cells are accessible to the user to enter the details like Item ID, Item Name, price and quantity. Watch the video below:

Here’s the complete VBA code to design a secure data entry form:

Option Explicit

Function validateForm() As Boolean
validateForm = True

If Range("F6") = "" Then
    MsgBox "The Item ID cannot be blank.", vbOKOnly + vbInformation, "Item ID"
    Range("F6").Interior.Color = vbRed
    Range("F6").Activate
    validateForm = False

ElseIf Range("F8") = "" Then
    MsgBox "The item name cannot be blank.", vbOKOnly + vbInformation, "Item Name"
    Range("F8").Interior.Color = vbRed
    Range("F8").Activate
    validateForm = False

ElseIf Range("F10") = "" Then
    MsgBox "The price cannot be blank.", vbOKOnly + vbInformation, "Price"
    Range("F10").Interior.Color = vbRed
    Range("F10").Activate
    validateForm = False

ElseIf Range("F12") = "" Then
    MsgBox "The quantity cannot be blank.", vbOKOnly + vbInformation, "Quantity"
    Range("F12").Interior.Color = vbRed
    Range("F6").Activate
    validateForm = False

End If

End Function

Function reset()
Range(“F6, F8, F10, F12”).ClearContents
Range(“F6, F8, F10, F12”).Interior.ColorIndex = 15
End Function

Sub transferData()

ActiveSheet.Unprotect “abc&&123”

If validateForm = True Then

Application.ScreenUpdating = False

Dim nextblankRow As Long, lastrow As Long

lastrow = Sheets(“TransferredData”).Range(“A” & Rows.Count).End(xlUp).Row

nextblankRow = lastrow + 1

With ThisWorkbook.Sheets(“TransferredData”)

    .Range("A" & nextblankRow).Value = nextblankRow - 1
    .Range("B" & nextblankRow).Value = Range("F6").Value
    .Range("C" & nextblankRow).Value = Range("F8").Value
    .Range("D" & nextblankRow).Value = Range("F10").Value
    .Range("E" & nextblankRow).Value = Range("F12").Value

End With

Sheets("TransferredData").Columns("A:E").AutoFit

Call reset
Else

Exit Sub

End If

ActiveSheet.Protect “abc&&123”

Application.ScreenUpdating = True
End Sub

Sub resetForm()
ActiveSheet.Unprotect “abc&&123”
Dim i As Integer
i = MsgBox(“Do you really wish to reset the form?”, vbQuestion + vbYesNo, “Reset Form”)
If i = vbYes Then
Call reset
End If
ActiveSheet.Protect “abc&&123”
End Sub

Option Explicit

Private Sub Workbook_Open()
Sheets(“ItemDetails”).Visible = xlVeryHidden
End Sub

Further reference:

Download a sample file for practice:

Color Codes