Using the User Form in VBA develop a code in wich: 1.The combo box have a list o

Question

Using the User Form in VBA develop a code in wich:

1.The combo box have a list of names from B4:B26

2.Once the student is selected and the calcular button pressed the ID number,name (column C), the average grade(numbers) and the final grade (letters) should be shown on “Reporte de Estudiante”

3. When you click the Reset button , the “Reporte de Estudiante” should be erased.

SP 13 CECS_2200_05 Computer Prc LM 12:30pm -2:00 pm EEPL Analisys de Registro ID Num Nombro 68849 Carbia Rodriguez, Jorgo 58080 Ciares Nieves, Abimelec 90 1 00 90 83 100 80 100 45 72 100 90 90 90 90 1791 David Maloy, Nicolas 74079 Ferrer Lind, William 74189 Ferreras Hernandez, Axel 70229 Fuentes Gonzalez, Angel 58071 Garay Gonzalez, lan 807T3 Juarbe Torres, Kevin 74873Lugo, Chirstopher 73414 Lugo Fuentes, Hermes 74149 Marrero Molina, Clenier 92 72 7 3 95 reporte de estudianvte # Lstudante Nombre y Apelido Nota final 10 97 12 13 14 15 16 17 10 19 100 100 90 94 76 95 90 8525 Mundo lglesias, Angel 1092 Peralta Grullon, Fernando 80488 Perez Correa, Samuel 8683 Perez Lopez, Ivan 70913 Ramos Pino, Angel 78631Rivora Rios, Luis 52 90 90 90 90 2 92 100 100 100 100

Explanation / Answer

From what I understand, you want to select a single student from the list and calculate their grades on pressing calculate. If you then select another student, the results are updated. If you press reset, all results disappear.

The following UserForm Code will help you do this, given that the data is in the exact same layout that you provided, i.e. Number in the first column, Name in the second, and Grades in the next four. Also, data should start from the 4th row, and be no longer than 23 rows, which is the range you specified.

---------------------------------------------------------------------------------------------------------------------------------------

Private Sub CalculateButton_Click()

Dim row As Long
Dim numColumn As Long
Dim nameColumn As Long
Dim dataColumn1 As Long
Dim dataColumn2 As Long
Dim dataColumn3 As Long
Dim dataColumn4 As Long
Dim Average As Double
Dim Grade As String
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")
numColumn = 1
nameColumn = 2
dataColumn1 = 3
dataColumn2 = 4
dataColumn3 = 5
dataColumn4 = 6

row = StudentComboBox.ListIndex

If row > -1 Then

row = row + 4
'Set Number
StudentNumber.Caption = ws.Cells(row, numColumn)
  
'Set Name
StudentName.Caption = ws.Cells(row, nameColumn)
  
'Calculate Average
Average = ws.Cells(row, dataColumn1) + ws.Cells(row, dataColumn2) + ws.Cells(row, dataColumn3) + ws.Cells(row, dataColumn4)
Average = Average / 4
'Set Average
AverageGrade.Caption = Average
  
'Calculate Grade
If Average > 90 Then
Grade = "A"
ElseIf Average > 75 Then
Grade = "B"
ElseIf Average > 60 Then
Grade = "C"
ElseIf Average > 50 Then
Grade = "D"
Else
Grade = "E"
End If
  
'Assign Grade
FinalGrade.Caption = Grade
  
End If

End Sub

Private Sub ResetButton_Click()

'Clear Number
StudentNumber.Caption = ""

'Clear Name
StudentName.Caption = ""

'Clear Average
AverageGrade.Caption = ""

'Clear Grade
FinalGrade.Caption = ""

End Sub

Private Sub UserForm_Initialize()

Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

'Empty StudentComboBox
StudentComboBox.Value = "Select Student"

For Each cLoc In ws.Range("StudentList")
With StudentReportForm.StudentComboBox
.AddItem cLoc.Value
End With
Next cLoc

'Empty Result Frame
StudentReport.Repaint

End Sub

----------------------------------------------------------------------------------------------------------------------------------------

You can increase/decrease the range by changing the "StudentList" named range in Excel, and put in different conditions for grading by making the changes in the code above.

I hope this helps.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.