Super Easy Secure Data Entry Form

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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.