Automatic Data Entry System in Excel

An Automatic Data Entry System in Excel is an advanced setup created using Microsoft Excel that allows users to enter data in one sheet and automatically store it in another sheet using Macros (VBA automation).

In this system, data is entered in a Home sheet, and with the help of a macro, it is automatically transferred to a Data sheet, which can be locked and hidden for security and data protection.

---

🔹 Simple Definition

An Automatic Data Entry System is a structured Excel solution that:

• Takes input from a user-friendly entry form (Home sheet)
• Automatically saves data into a database sheet
• Secures the data by locking and hiding the sheet
• Reduces manual copy-paste work

---

🔹 Sheets Included in the System

🏠 1. Home Sheet

• Used for entering data
• Simple and user-friendly interface
• Can include input fields like Name, Date, Amount, etc.
• Button (macro) to submit data

📂 2. Data Sheet

• Stores all records automatically
• Works as a database
• Gets updated using macros
• Can be hidden and locked for security

---

🔹 How it Works

• User enters data in the Home sheet
• Clicks on a Submit button
• Macro automatically copies data
• Data gets pasted into the Data sheet
• Data sheet remains hidden and protected

---

🔹 Key Features

• ✔ Fully automated using VBA Macros
• ✔ Secure data storage (hidden & locked sheet)
• ✔ No manual copy-paste required
• ✔ User-friendly interface
• ✔ Suitable for data entry systems

---

🔹 Why use Excel for Automatic Data Entry?

• ✔ Saves time and effort
• ✔ Reduces human errors
• ✔ Easy to customize
• ✔ No need for expensive software
• ✔ Ideal for small businesses and offices

---

🔹 Real-life Uses

• Office data entry systems
• Student record management
• Billing and transaction entry
• Small database systems
• Form-based data collection

Formula

Amount = Click on Rate * Click on Quantity

Discount = Click on Amount * 5%

Net Total = Click on Amount – Click on Discount

Macros Steps

For Record Macro “Save Data”

1. First Turn on Developer Tab
2. Complete Data Entry
3. After That Click on Developer Tab & Click on Record Macro & Name = SaveData
4. Now Select in Home Sheet from Sr No to Net Total & Copy that range of Data
5. Now Go to Data Sheet & Click in J1 Cell
6. Press Ctrl + Down Arrow, Ctrl + Left Arrow, Ctrl + Up Arrow
7. Now Click on Use Relative References
8. Now Press Only Down Arrow
9. Press Ctrl+Alt+V & Now Click on Values Option and Click on Transpose Option & Click on OK
10. Set Data Formatting According to You
11. Press Right Arrow to Go to J2 Cell
12. Now Go to Developer Tab & Turn Off Use Relative References Option
13. Now Go to Dashboard Sheet & Select Data from Sr No to Quantity & Press Delete Button
14. Now Click in Date Cell & Stop Recording (Macro Recording has been Stopped)
15. Now Select SaveData Shape & Right Click on That Shape (TextBox)
16. Now Click on Assign Macro & Click on SaveData Macro & Click on Ok

Code for Locked Sheet (Data)

Click On Developer Tab and Click on Macros

Click on Edit as Given Below

Now Enter Code Here To Lock Sheet Properly

Code Must Be Above Range

ActiveWorkbook.Unprotect “1234”
Sheets(“Data”).Visible = True

Code Must Be Below Range

Sheets(“Data”).Visible = False
ActiveWorkbook.Protect “1234”

Now Save as Macro Enabled Workbook

Enable Macros To Work Properly

If You Need a Help – Watch Video