Page 445 - Computer Software Application TP - Volume 1

COMPUTER SOFTWARE APPLICATION - CITS

3 Determining the Next Empty Row:
• row = WorksheetFunction.CountA(Range(“A:A”)) + 1: This line calculates the next empty row in column
A of the active worksheet (Sheet4) by counting the number of non-empty cells in column A and adding 1. It
uses the CountA function of the WorksheetFunction object.
4 Assigning Values to Cells:
• The subsequent lines assign values to cells in the active worksheet (Sheet4) based on the inputs provided
in various text boxes (txtid, txtname, txtbp, txtda, txthra, txtded, txtts, txtns).
• Each value is assigned to a specific column in the determined row, allowing for the storage of employee
information and salary details.

5 Data Entry:
• The data from the text boxes (txtid, txtname, etc.) are entered into the next empty row in the specified
columns of Sheet4.

This code essentially allows users to enter employee details and their corresponding salary information into Sheet4
of the Excel workbook by clicking the “Submit” button (cmdsubmit). It automates the process of determining the
next empty row and filling in the necessary information accordingly.
• Step 8: Add a method to close the form when the user clicks the Cancel button.
Private Sub cmdcancel_Click()
Unload Me
End Sub

The above VBA code is associated with the click event of a button named cmdcancel. Here’s what the code does:
1 Unload Form:
• Unload Me: This line unloads (closes) the user form associated with the VBA code. The Me keyword refers
to the current instance of the form where the VBA code is located. Unload Me effectively closes the form
when the user clicks the “Cancel” button (cmdcancel).
• Step 9: Add a method to clear the form when the user clicks the Clear button. Private Sub cmdclear_Click()
txtid.Value = “ “

txtname.Value = “ “
txtbp.Value = “ “
txtda.Value = “ “
txthra.Value = “”

txtded.Value = “ “
txtts.Value = “ “
txtns.Value = “ “
txtid.SetFocus
End Sub

This action provides a convenient way for users to clear out all input fields and start fresh if needed. when the user
clicks the “Clear” button (cmdclear), all the text boxes’ contents are cleared, and the focus is set back to the txtid
text box, ready for new input or interaction.
• Step 10: Execute the form by clicking the “Run” button. Enter the values into the form and click the ‘Calculate’
button and the click the ‘Submit’ button. Automatically the values will flow into the worksheet as shown in the
following screenshot.

430

CITS
CITS : IT & ITES - Computer Software Application - Exercise 71 : IT & ITES - Computer Software Application - Exercise 71