---

Private Sub UserForm_Initialize()
    ' Заголовок формы
    Me.Caption = "Список студентов"
    Me.Width = 500
    Me.Height = 350
    
    ' Создаем элементы управления вручную
    CreateControls
    LoadCourses
    LoadStudents
End Sub

Sub CreateControls()
    ' --- Метки ---
    With Me.Controls.Add("Forms.Label.1")
        .Caption = "Фамилия"
        .Left = 10
        .Top = 10
        .Width = 70
    End With
    
    With Me.Controls.Add("Forms.Label.1")
        .Caption = "Имя"
        .Left = 10
        .Top = 40
        .Width = 70
    End With
    
    With Me.Controls.Add("Forms.Label.1")
        .Caption = "Специальность"
        .Left = 10
        .Top = 70
        .Width = 70
    End With
    
    ' --- Поля ввода ---
    With Me.Controls.Add("Forms.TextBox.1", "txtFam")
        .Left = 90
        .Top = 8
        .Width = 120
    End With
    
    With Me.Controls.Add("Forms.TextBox.1", "txtName")
        .Left = 90
        .Top = 38
        .Width = 120
    End With
    
    With Me.Controls.Add("Forms.ComboBox.1", "cboSpec")
        .Left = 90
        .Top = 68
        .Width = 120
        .Style = fmStyleDropDownList
    End With
    
    ' --- Список студентов ---
    With Me.Controls.Add("Forms.ListBox.1", "lstFam")
        .Left = 230
        .Top = 8
        .Width = 150
        .Height = 120
    End With
    
    ' --- Кнопки ---
    With Me.Controls.Add("Forms.CommandButton.1", "btnAdd")
        .Caption = "Добавить"
        .Left = 10
        .Top = 110
        .Width = 80
    End With
    
    With Me.Controls.Add("Forms.CommandButton.1", "btnSave")
        .Caption = "Записать"
        .Left = 100
        .Top = 110
        .Width = 80
    End With
    
    With Me.Controls.Add("Forms.CommandButton.1", "btnDelete")
        .Caption = "Удалить"
        .Left = 190
        .Top = 110
        .Width = 80
    End With
    
    With Me.Controls.Add("Forms.CommandButton.1", "btnExit")
        .Caption = "Выход"
        .Left = 280
        .Top = 110
        .Width = 80
    End With
    
    With Me.Controls.Add("Forms.CommandButton.1", "btnSortAsc")
        .Caption = "Сортировка ↑"
        .Left = 10
        .Top = 150
        .Width = 120
    End With
    
    With Me.Controls.Add("Forms.CommandButton.1", "btnSortDesc")
        .Caption = "Сортировка ↓"
        .Left = 140
        .Top = 150
        .Width = 120
    End With
End Sub

Sub LoadCourses()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Курсы")
    Dim cbo As Object
    Set cbo = Me.Controls("cboSpec")
    
    cbo.Clear
    Dim i As Integer
    For i = 1 To 4
        If ws.Cells(i, 1) <> "" Then cbo.AddItem ws.Cells(i, 1)
    Next
End Sub

Sub LoadStudents()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Список")
    Dim lst As Object
    Set lst = Me.Controls("lstFam")
    
    lst.Clear
    Dim i As Integer
    i = 2
    Do While ws.Cells(i, 1) <> ""
        lst.AddItem ws.Cells(i, 1)
        i = i + 1
    Loop
End Sub

Private Sub btnAdd_Click()
    Me.Controls("txtFam").Text = ""
    Me.Controls("txtName").Text = ""
    Me.Controls("cboSpec").Value = ""
    Me.Controls("txtFam").SetFocus
End Sub

Private Sub btnSave_Click()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Список")
    
    Dim fam As String, name As String, spec As String
    fam = Me.Controls("txtFam").Text
    name = Me.Controls("txtName").Text
    spec = Me.Controls("cboSpec").Value
    
    If fam = "" Or name = "" Or spec = "" Then
        MsgBox "Заполните все поля!"
        Exit Sub
    End If
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    
    ws.Cells(lastRow, 1) = fam
    ws.Cells(lastRow, 2) = name
    ws.Cells(lastRow, 3) = spec
    
    LoadStudents
    btnAdd_Click
    MsgBox "Добавлено!"
End Sub

Private Sub btnDelete_Click()
    Dim lst As Object
    Set lst = Me.Controls("lstFam")
    
    If lst.ListIndex = -1 Then
        MsgBox "Выберите студента!"
        Exit Sub
    End If
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Список")
    Dim fam As String
    fam = lst.Value
    
    Dim i As Integer
    i = 2
    Do While ws.Cells(i, 1) <> ""
        If ws.Cells(i, 1) = fam Then
            ws.Rows(i).Delete
            LoadStudents
            MsgBox "Удалено!"
            Exit Sub
        End If
        i = i + 1
    Loop
End Sub

Private Sub btnSortAsc_Click()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Список")
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    If lastRow > 1 Then
        ws.Range("A1:C" & lastRow).Sort Key1:=ws.Range("A1"), Order1:=xlAscending, Header:=xlYes
        LoadStudents
        MsgBox "Отсортировано ↑"
    End If
End Sub

Private Sub btnSortDesc_Click()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Список")
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    If lastRow > 1 Then
        ws.Range("A1:C" & lastRow).Sort Key1:=ws.Range("A1"), Order1:=xlDescending, Header:=xlYes
        LoadStudents
        MsgBox "Отсортировано ↓"
    End If
End Sub

Private Sub btnExit_Click()
    Unload Me
End Sub

Private Sub lstFam_Click()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Список")
    Dim lst As Object
    Set lst = Me.Controls("lstFam")
    
    If lst.ListIndex = -1 Then Exit Sub
    
    Dim i As Integer
    i = 2
    Do While ws.Cells(i, 1) <> ""
        If ws.Cells(i, 1) = lst.Value Then
            Me.Controls("txtFam").Text = ws.Cells(i, 1)
            Me.Controls("txtName").Text = ws.Cells(i, 2)
            Me.Controls("cboSpec").Value = ws.Cells(i, 3)
            Exit Sub
        End If
        i = i + 1
    Loop
End Sub