Protocol - All Sub

Sub Main()
    'StartUI.cb1_Click
    'cb1_Click

    'Dim lastRow As Integer
    'lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    'StartUI.Show

    'Dim num As Integer

    'Sheets.Add(Before:=Sheets("Sheet0")).Name = "Protocol"
    'CopyData
    'InsertColumns
    'FormatHeaders (lastRow)

    'PracticalAndThereticalPoints (lastRow)
    'Dim percentPractical, percentTheoretical As Integer
    'percentPractical = num
    'percentPractical = Application.InputBox("Enter a percentage for a practical exam")
    'percentTheoretical = Application.InputBox("Enter a percentage for a theoretical exam")

    'CoursePoints lastRow, percentPractical, percentTheoretical
    'Grade (lastRow)

End Sub


Function CoursePoints(lastRow, percentPractical, percentTheoretical As Integer)
    Dim isParticipate As Boolean
    For r = 2 To lastRow
        isNotParticipate = Range("D" & r) = "NO" And Range("E" & r) = "NO"
        If isNotParticipate Then
            Range("B" & r) = "Did not appear for the exam!"
        Else
            Range("B" & r) = _
            "=$D" & r & "*" & percentPractical & "/ 100 +" & " $E" & r & "*" & percentTheoretical & " / 100"
        End If

    Next
End Function

Sub PracticalAndThereticalPoints(lastRow As Integer)
    For r = 2 To lastRow
        Range("D" & r) = _
        "=VLOOKUP(A" & r & " ,'Practical Points'!A:B,2,FALSE)"
        Range("E" & r) = _
        "=VLOOKUP(A" & r & " ,'Theoretical Points'!A:B,2,FALSE)"

        If Range("D" & r) = 0 And Range("E" & r) = 0 Then
            Range("D" & r) = "NO"
            Range("E" & r) = "NO"
        End If
    Next
End Sub

Sub Grade(lastRow, pointsFor5, pointsFor3 As Integer)
    For r = 2 To lastRow
        value = Range("G" & r)

        If Range("B" & r) = "Did not appear for the exam!" Then
            Range("C" & r) = ""
        ElseIf Range("B" & r) >= 100 And value Then
            Range("C" & r) = 6
        ElseIf Range("B" & r) <= pointsFor5 Then
            Range("C" & r) = "=3+2*(B" & r & "-" & pointsFor3 & ")/(" & pointsFor5 & "-" & pointsFor3 & ")"
            If Range("C" & r) <= 2 Then
            Range("C" & r) = 2
            End If
        Else
            'Range("C" & r) = "=5+(B" & r & "-70)/(95-70)"
            Range("C" & r) = "=5+(B" & r & "-" & pointsFor5 & ")/(95-" & pointsFor5 & ")"
        End If
    Next

End Sub

Sub Statistics(allParticipants, lastRow As Integer)


End Sub


Function CopyData()
      Dim col, data As Variant

      col = GetColAddress("Sheet0", "Username")
      If col = False Then
        MsgBox "Username not found"
      Else
        Sheets("Sheet0").Select
        Columns(col).Select
        Selection.Copy
        Sheets("Protocol").Select
        Columns(1).Select
        ActiveSheet.Paste
      End If


End Function

Function GetColAddress(sheetName As String, target As String)
      Dim col As Range

      Set col = Sheets(sheetName).Rows(1).Find(What:=target)

      If col Is Nothing Then
        GetColAddress = False
      Else
        GetColAddress = col.Column

      End If

End Function

Sub InsertColumns()
    Sheets("Protocol").Select
    Columns("B:B").Select
    'Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    'Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    'Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    'Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    'Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Common Points"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Grade"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Practical Points"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Theoretical Points"

    'Rows(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    'Rows(1).EntireRow.Delete
End Sub

Sub FormatHeaders(lastRow As Integer)
    Range("A1:E" & lastRow).Select
    Selection.ColumnWidth = 30
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1:E1").Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
End Sub