Скачать или смотреть Excel VBA Bangla Tutorial 53 : Student data entry |search| update| result sheet |using user form

To create a student data entry, search, update, and result sheet using a userform in Excel VBA, you can follow these steps:

To create a student data entry system with search, update, and result sheet functionality using a user form in Excel VBA, you can follow these general steps:

Create a user form with input fields for student data (such as name, ID, age, and grade) and buttons for search, update, and view result sheet.
Write VBA code to handle events triggered by the user form, such as clicking on the search or update button.
Store the student data in a worksheet or database, and use VBA to read from and write to this data source.
When the user clicks the search button, use VBA to search for the entered student data and display the matching record(s) in the user form.
When the user clicks the update button, use VBA to update the corresponding record in the data source with the new data entered in the user form.
When the user clicks the view result sheet button, use VBA to generate a report or view that shows all the student data in the data source.
Note that this is a high-level overview, and there are many details that may depend on your specific requirements and data source.




Create a new Excel workbook and press "Alt + F11" to open the Visual Basic Editor.

In the Visual Basic Editor, go to "Insert" "Userform" to create a new userform.

Add text boxes and labels to the userform to capture student data, such as name, roll number, age, and grade.

Add a command button to the userform to submit the student data to a worksheet.

Use VBA code to transfer the student data to a worksheet. For example:

VBA
Copy code
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = Worksheets("Data")

With ws
Dim lastRow As Long
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

.Cells(lastRow + 1, 1).Value = Me.TextBox1.Value
.Cells(lastRow + 1, 2).Value = Me.TextBox2.Value
.Cells(lastRow + 1, 3).Value = Me.TextBox3.Value
.Cells(lastRow + 1, 4).Value = Me.TextBox4.Value
End With

Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
End Sub
Add another command button to the userform to search for a student by roll number.

Use VBA code to search for the student data in the worksheet. For example:

VBA
Copy code
Private Sub CommandButton2_Click()
Dim ws As Worksheet
Set ws = Worksheets("Data")

Dim rollNumber As String
rollNumber = InputBox("Enter Roll Number:")

If rollNumber not equal to "" Then
Dim found As Range
Set found = ws.Columns("B").Find(what:=rollNumber, LookIn:=xlValues, lookat:=xlWhole)

If found Is Nothing Then
MsgBox "Roll Number not found."
Else
Me.TextBox1.Value = found.Offset(0, -1).Value
Me.TextBox2.Value = found.Value
Me.TextBox3.Value = found.Offset(0, 1).Value
Me.TextBox4.Value = found.Offset(0, 2).Value
End If
End If
End Sub
Add another command button to the userform to update the student data.

Use VBA code to update the student data in the worksheet. For example:

VBA
Copy code
Private Sub CommandButton3_Click()
Dim ws As Worksheet
Set ws = Worksheets("Data")

Dim rollNumber As String
rollNumber = Me.TextBox2.Value

If rollNumber "not equal to sign" "" Then
Dim found As Range
Set found = ws.Columns("B").Find(what:=rollNumber, LookIn:=xlValues, lookat:=xlWhole)

If found Is Nothing Then
MsgBox "Roll Number not found."
Else
found.Offset(0, -1).Value = Me.TextBox1.Value
found.Offset(0, 1).Value = Me.TextBox3.Value
found.Offset(0, 2).Value = Me.TextBox4.Value
End If
End If
End Sub
Add another command button to the userform to display the student result sheet.