Automated Student’s Registration Form in Excel and VBA

Form
Form

This article is primarily focused on developing an Automated Student’s Registration applications using the Visual Basic for Applications (VBA) programming language. With Excel VBA you can create a user form to to automate the data entry task using what is called a macro.

Student’s registration form is just taken as example to develop the data entry form. Once you complete this article, you will be able to develop a data entry application with all required features.

In this article, you will learn:

  • Create Home, Database and Support Sheet
  • User Interface for Data Entry
  • Import Custom Calendar and Icons
  • Design Form and assign properties to all controls
  • Code to Reset and Initialize the User Form
  • Function to Validate Email ID and Other Inputs done by User
  • Sub Procedure to Create a folder
  • Procedure to upload the Passport Size Photo and Consolidate the pictures in folder
  • Assign the path of uploaded pictures to Image Control
  • Sub Procedure to Submit or Transfer the Data from user-form to database sheet
  • Sub Procedure to Edit and Delete the existing records
  • Assign the Macro to run the form

So in this article, a lot to know and a lot to cover. Please read this article carefully and follow the instructions to develop the Data Entry Form with Image.

Step 1 – Creating Excel File

  1. Open the Excel Application
  2. Create a New Blank Workbook
  3. Save the Excel file with the Name ‘Student Registration Form.xlsm’

Step 2 – Creating Home Sheet

  1. Add three new worksheets in this file
  2. Rename the first sheet to ‘Home’
  3. Format the sheets and add a Rounded Rectangle with caption as per below image
Home
Home

Step 3 – Creating Database Sheet and Columns

  1. Rename the second worksheets to ‘Database’
  2. Add the required columns in ‘Database’ worksheet
  3. Do the required formatting as per below image
Database
Database

Step 4 – Creating Support Sheet

  1. Rename the third worksheets to ‘Support’
  2. Add the Course details in Column A with column header ‘Courses’
  3. Apply the formatting as per below image
Support
Support

Step 5 – Design the Data Entry Form in Visual Basic Environment window

  1. Insert a UserForm
  2. Design the Data Entry form as per below image
Form
Form

3. Place all the Frames, Controls as per above image. Make sure we have utilized two hidden text-boxes to store row number and image path. Please create both the text box anywhere in the form. Details have been given in below mentioned properties.

Once you create controls with hidden text-boxes, set the Properties of Form and Controls as per below mentioned details.

UserForm Properties

Name: frmDataEntry
BackColor: &H00FFFFFF&
Caption : Student Registration Form
Height: 484
Width: 573

Frame1 – Frame1 and other controls properties

Name: Frame1
BackColor: &H8000000E&
BorderColor: &H00000080&
BorderStyle: 1-frmBorderStyleSingle
Caption: Enter Details
Font: Tahoma, Regular, 12
ForeColor: &H00000080&
Height: 252
Width: 534

Controls in Frame1

Label Caption: Student’s Name

Control Type: TextBox
Name: txtStudentName
BackColor: &H80000005&
BorderColor: &H00000080&
BorderStyle: 1-frmBorderStyleSingle
Font: Tahoma, Regular, 8
ForeColor: &H80000008&
Height: 20
Width: 192
TabIndex: 0

Label Caption: Father’s Name

Control Type: TextBox
Name: txtFatherName
BackColor: &H80000005&
BorderColor: &H00000080&
BorderStyle: 1-frmBorderStyleSingle
Font: Tahoma, Regular, 8
ForeColor: &H80000008&
Height: 20
Width: 192
TabIndex: 1

Label Caption: Date Of Birth

Control Type: TextBox
Name: txtDOB
BackColor: &H80000005&
BorderColor: &H00000080&
BorderStyle: 1-frmBorderStyleSingle
Font: Tahoma, Regular, 8
ForeColor: &H80000008&
Height: 20
Locked:True
Width: 192
TabIndex: 2

Icon for Calendar in Date of Birth Text Box

Control Type: Image
Name: imgCalendar
BackStyle: 0-frmBackStyleTransparent
BorderStyle: 0-frmBorderNone
Height: 20
PictureAlignment: 2-frmPictureAlignmentCenter
PictureSizeMode: 1-frmPictureSizeModeStretch
SpecialEffect: 0-frmSpecialEffectFlat
Width: 20

Label Caption: Gender

1. Control Type: Option Button
Name: optFemale
Caption: Female
Value:False
TabIndex: 3
2. Control Type: Option Button
Name: optMale
Caption: Male
Value:False
TabIndex: 4

Label Caption: Course Applied

Control Type: ComboBox
Name: cmbCourse
BackColor: &H80000005&
BorderColor: &H00000080&
BorderStyle: 1-frmBorderStyleSingle
Font: Tahoma, Regular, 8
ForeColor: &H80000008&
Height: 20
Width: 192
TabIndex: 5

Label Caption: Mobile Number

Control Type: TextBox
Name: txtMobile
BackColor: &H80000005&
BorderColor: &H00000080&
BorderStyle: 1-frmBorderStyleSingle
Font: Tahoma, Regular, 8
ForeColor: &H80000008&
Height: 20
Width: 192
TabIndex: 6

Label Caption: Email ID

Control Type: TextBox
Name: txtEmail
BackColor: &H80000005&
BorderColor: &H00000080&
BorderStyle: 1-frmBorderStyleSingle
Font: Tahoma, Regular, 8
ForeColor: &H80000008&
Height: 20
Width: 192
TabIndex: 7

Hidden TextBox (to store row number – for internal purpose only)

Control Type: TextBox
Name: txtRowNumber
Visible: False

Image Control

Control Type: Image
Name: imgStudent
BackColor: &H80000005&
BorderColor: &H00000080&
BorderStyle: 1-frmBorderStyleSingle
Height: 80
PictureAlignment: 2-frmPictureAlignmentCenter
PictureSizeMode: 1-frmPictureSizeModeStretch
SpecialEffect: 0-frmSpecialEffectFlat
Width: 80

Hidden TextBox (to store image path – for internal purpose only)

Control Type: TextBox
Name: txtImagePath
Visible: False

Command Button – To browse and upload the image

Control Type: CommandButton
Name: cmdLoadImage
BackColor: &H80000005&
Caption: Upload Image…
Font: Tahoma, Regular, 8
TabIndex: 9

Label Caption: Address :

Control Type: TextBox
Name: txtAddress
BackColor: &H80000005&
BorderColor: &H00000080&
BorderStyle: 1-frmBorderStyleSingle
Font: Tahoma, Regular, 8
ForeColor: &H80000008&
Height: 63
MultiLine: True
ScrollBars: 2-frmScrollBarsVertical
TabIndex: 10
Width: 192

Command Button – Submit and Reset

Control Type: CommandButton
Name: cmdSubmit
Accelerator: s
BackColor: &H80000005&
Caption: Submit
Font: Tahoma, Regular, 9
Height: 20
TabIndex: 11
Width:60

Control Type: CommandButton
Name: cmdReset
Accelerator: r
BackColor: &H00FFC0FF&
Caption: Reset
Font: Tahoma, Regular, 9
Height: 20
TabIndex: 12
Width:60

Frame2 – Frame2 and other controls properties

Name: Frame2
BackColor: &H8000000E&
BorderColor: &H00000080&
BorderStyle: 1-frmBorderStyleSingle
Caption: Database
Font: Tahoma, Regular, 12
ForeColor: &H00000080&
Height: 186
Width: 534

Command Button – Edit and Delete

Control Type: CommandButton
Name: cmdEdit
Accelerator: e
BackColor: &H00FFC0C0&
Caption: Edit
Font: Tahoma, Regular, 9
Height: 20
TabIndex: 1
Width:60

Control Type: CommandButton
Name: cmdDelete
Accelerator: d
BackColor: &H00C0E0FF&
Caption: Delete
Font: Tahoma, Regular, 9
Height: 20
TabIndex: 2
Width:60

List-box to Show the Data

Control Type: ListBox
Name: lstDatabase
BorderColor: &H00000080&
BorderStyle: 1-frmBorderStyleSingle
Font: Tahoma, Regular, 8
ForeColor: &H80000008&
Height: 137
TabIndex: 0
Width:517

Now, we have prepare the Form, created required controls and set the properties as given below. Let’s move to the next step.

4. Import the MyCalendar custom calendar control from the Support Folder.

5. Insert a new module. To insert click on Insert Menu – > Module.

6. Change the name of Module1 to mdDataEntry in properties window.

7. Add the below mentioned code on Double Click event of txtDOB to show the Calendar and pick the DOB from custom calendar control.

8. Add the below mentioned code on Click event of imgCalendar to show the Calendar and pick the DOB from custom calendar control.

9. Let’s double click on mdDataEntry module and add the code to perform Reset, Validate, Load Image, Transfer, Edit, Delete and others.

Code to Reset and Initialize the form with default value

Code to Validate the structure of email entered by user

Function to Browse and Select the Image

Sub Procedure to create a folder named ‘Images’ at the same location where Excel Files has been saved.

Sub Procedure to Save the selected image in Images folder and load it to Image control imgStudent

Function to Validate data entered by user

Sub Procedure to Transfer the Data from Form to Database sheet

Function to find the selection in lstDatabase

Sub Procedure to Show the Form

Now, we have created all the Sub Procedures and Functions. Let’s call these functions and procedures on click events and form intilization.

Go to Form and double click on ‘cmdLoadImage’ command button to call the ‘LoadImage’ sub procedure.

Call the Reset_Form sub-procedure on Form Initialization

Assign the code on Click event of Submit Button

Assign the code on Click event of Reset Button

Add the below code on double click of lstDatabase to edit the record

Write the below code on click event of cmdDelete command button

Write the below code on click event of cmdEdit command button

Now, we have successfully assigned and called all the required functions and procedures on different events. Let’s move ‘Home’ sheet in Excel and assign the macro ‘Show_Form’ on rectangular button so that user can click there to launch the form.

Student’s Registration Form is ready. You can test this form and start using.

Please watch the Step by Step Tutorial on YouTube.

Please download the Support and Excel file used in this tutorial.

Download
Click to download

Thanks!

6 COMMENTS

  1. Thanks for sharing the knowledge

    I made a small correction to your code and it works perfectly.

    lstDatabase.ColumnWidths = “20 pt;85 pt;85 pt;65 pt;50 pt;60 pt;65 pt;60 pt;0 pt;0 pt;0 pt;0 pt”

LEAVE A REPLY

Please enter your comment!
Please enter your name here