Notes

Sub create_nextweek()
'

'

'
Dim next_week_name As String

If MsgBox("Are you sure you want to create timetable for next week?", vbYesNo, "Create Next Week") = vbNo Then Exit Sub
ActiveSheet.Select
next_week_name = Range("$R$3").Value
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Name = next_week_name
' now the activesheet is the new sheet

End Sub


Sub clear_trial()
Dim i As Integer

For i = 1 To 500
If Cells(i, 9).Value = "Trial Class" Then
Cells(i, 11).ClearContents
Cells(i, 12).ClearContents
Cells(i, 13).ClearContents
Cells(i, 14).ClearContents
End If

Next i

End Sub


Sub tidy()
Dim i As Integer
Dim col_no As Integer

Dim count_swap As Integer
Dim seat_per_class As Integer

col_no = 9
count_swap = 1
seat_per_class = 8
'8 seats per class

Do Until count_swap = 0
count_swap = 0
For i = 1 To seat_per_class


If IsEmpty(Cells(i, col_no)) And Not IsEmpty(Cells(i + 1, col_no)) Then
count_swap = count_swap + 1
Cells(i, col_no).Value = Cells(i + 1, col_no).Value
Cells(i + 1, col_no).ClearContents
End If

Next i

Loop

End Sub

Sub create_class()


Dim nextWS As Worksheet
Dim originalWS As Worksheet
Dim start_row As Integer
Dim start_column As Integer
Dim pointer_row As Integer
Dim pointer_column As Integer
Dim top_marker As String
Dim SID As Variant
Dim i As Integer
Dim no_week As Integer
Dim seat_per_class As Integer
Dim spreadsheet_height As Integer
Dim empty_seat As Integer


spreadsheet_height = 1000
seat_per_class = 8
'8 seats per class

start_row = 0
no_week = 0
start_column = 4 'this is the column of the SID

SID = InputBox("Please input the SID Number that you want to add classes for")

For i = 1 To spreadsheet_height

If Cells(i, start_column) = "SID" & SID Then
start_row = i
Exit For
End If
Next i
'find out which row is the inputed SID

If start_row = 0 Then
MsgBox "Cannot find the input SID"
Exit Sub
End If

If MsgBox("Are you sure you want to add classes for " & Cells(i, 5).Value & " ?", vbYesNo, "Create Next Week") = vbNo Then Exit Sub


top_marker = "top"


Set nextWS = ActiveSheet
Set originalWS = ActiveSheet

'start looping through weeks, checking if all have empty seats
Do Until nextWS Is Nothing

pointer_row = start_row
pointer_column = start_column

On Error GoTo ErrorHandler
Set nextWS = ActiveWorkbook.Worksheets(Cells(3, 18).Value)
'cells(3,18) is next week's value at R3



Do Until nextWS.Cells(pointer_row - 1, pointer_column).Value = top_marker
pointer_row = pointer_row - 1
Loop
'after this loop, pointer at the first seat

nextWS.Activate


empty_seat = -1

For i = 0 To seat_per_class - 1
If IsEmpty(nextWS.Cells(pointer_row + i, pointer_column).Value) Then
empty_seat = pointer_row + i
End If
Next i

If empty_seat = -1 Then
MsgBox "Please check - no empty seat"
Exit Sub
End If


Loop
'end - looping through each week

Add_seat:

Set nextWS = originalWS
nextWS.Activate

'checking done, now start looping through weeks, adding seats
Do Until nextWS Is Nothing


pointer_row = start_row
pointer_column = start_column

On Error GoTo ErrorHandler2
Set nextWS = ActiveWorkbook.Worksheets(Cells(3, 18).Value)
'cells(3,18) is next week's value at R3



Do Until nextWS.Cells(pointer_row - 1, pointer_column).Value = top_marker
pointer_row = pointer_row - 1
Loop
'after this loop, pointer at the first seat

nextWS.Activate




For i = 0 To seat_per_class - 1
If IsEmpty(nextWS.Cells(pointer_row + i, pointer_column).Value) Then
nextWS.Cells(pointer_row + i, pointer_column).Value = "SEATTTTT"
' add seats here
Exit For
End If
Next i

no_week = no_week + 1

Loop
'looping through each week




ErrorHandler:

Resume Add_seat


ErrorHandler2:

Resume Finish_add

Finish_add:

MsgBox no_week & " classes have been added for SID" & SID


End Sub

Sub protect()
'
' protect Macro
'

'
Range("L21").Select
Selection.Locked = False
Selection.FormulaHidden = False
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Cells(1, 1).Locked

End Sub