23. DATOS VINCULADOS PARA LA GESTIÓN DE ALUMNOS – Control Docente

INSTRUCCIONES

He desarrollado para demostración un sistema de control de Alumnos mal llamado “Control Docente”. Este Libro Gestiona la inscripción, inserta nuevos alumnos y controla la asistencia, prácticos, exámenes y entrega la Nota Final. Ordena los alumnos por Curso, Apellido Paterno, Apellido Materno.

Private Sub CommandButton1_Click() ‘Boton Insertar Alumno Nuevo

  Application.ScreenUpdating = False

  Application.Calculation = xlCalculationManual

  Dim fil As Long: Dim col As Long: Dim nro As Integer

  Me.TextBox1.SetFocus

  nro = 1

  fil = [B8].Row

  col = [B8].Column

  If ([B8] <> "") Then

    Do While (Cells(fil, col).Value <> "")

      fil = fil + 1

      nro = nro + 1

    Loop

  End If

  Call copiarFila("asistencia", fil, 32, nro)

  Call copiarFila("practicos", fil, 33, nro)

  Call copiarFila("examenes", fil, 31, nro)

  Call copiarFila("notasFinales", fil, 11, nro)

  Call ordenar("notasFinales", fil, 9)

  Call ordenar("examenes", fil, 28)

  Call ordenar("practicos", fil, 28)

  Call ordenar("asistencia", fil, 28)

  UserForm1.Hide

  Call limpiarFormulario

  With Worksheets("notasFinales")

    .Range("G8").FormulaLocal = "=SIERROR(REDONDEAR(Asistencia!AE8*K$2;0);0)"

    .Range("H8").FormulaLocal = "=SIERROR(REDONDEAR(Practicos!AF8*K$3;0);0)"

    .Range("I8").FormulaLocal = "=SIERROR(REDONDEAR(Examenes!AC8*K$4;0);0)"

    .Range("G8:I8").AutoFill Destination:=Range(Cells(8, 7), Cells(fil + 1, 9)), Type:=xlFillDefault

  End With

  Worksheets("Asistencia").Select

  Application.Calculation = xlCalculationAutomatic

  Application.ScreenUpdating = True

End Sub

Private Sub copiarFila(hoja As String, fil As Long, col As Long, nro As Integer)

  Dim miRango As String

  Worksheets(hoja).Activate

  Worksheets(hoja).Range(Cells(fil, 2), Cells(fil, col)).Copy Destination:=Range(Cells(fil + 1, 2), Cells(fil + 1, col))

  Application.CutCopyMode = False

  Cells(fil, 2).Value = nro

  Cells(fil, 3).Value = StrConv(TextBox3.Text, vbProperCase)

  Cells(fil, 4).Value = StrConv(TextBox4.Text, vbProperCase)

  Cells(fil, 5).Value = StrConv(TextBox2.Text, vbProperCase)

  Cells(fil, 6).Value = StrConv(TextBox1.Text, vbProperCase)

End Sub

Private Sub limpiarFormulario()

  TextBox1.Text = ""

  TextBox2.Text = ""

  TextBox3.Text = ""

  TextBox4.Text = ""

End Sub

Private Sub CommandButton2_Click() ‘Boton de Cancelar

  Me.TextBox1.SetFocus

  Me.Hide

  Call limpiarFormulario

End Sub

Private Sub ordenar(hoja As String, fil As Long, col As Long)

  Application.ScreenUpdating = False

  Application.Calculation = xlCalculationManual

  Range(Cells(8, 3), Cells(fil, col)).Select

  With ActiveWorkbook.Worksheets(hoja).Sort

    .SortFields.Clear 'SortOn=xlSortOnValues ordena por valores

    .SortFields.Add Key:=Range("F8:F17"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    .SortFields.Add Key:=Range("C8:C17"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    .SortFields.Add Key:=Range("D8:D17"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    .SortFields.Add Key:=Range("E8:E17"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    .SetRange Range(Cells(8, 3), Cells(fil, col))

    .Header = xlNo 'sin encabezado

    .MatchCase = False 'no distingue entre mayusculas y minusculas

    .Orientation = xlTopToBottom

    '.SortMethod = xlPinYin

    .Apply

  End With

  'Worksheets(hoja).Range("AC4").Value = Application.WorksheetFunction.CountIf(Range("G8:AB8"), "")

  Application.Calculation = xlCalculationAutomatic

  Application.ScreenUpdating = True

End Sub


VIDEO DEMOSTRATIVO: