Dim f As Integer 'font
Dim cm As Integer 'camera number
Dim co As Integer 'color index
Dim s As Integer 'score selection
Dim d As Date 'Date last changed in column E of main sheet
Dim v As String 'Visibility chosen
d = Now() 'captures the date and time of click
cm = Val(txtCamera.Text)
s = Val(cboScore.Text) 'converts the selection to a value
v = cboVisibility.Text 'stores the selected visibility as a string variable
fov = txtFieldofView.Text 'stores the field of view text
Select Case s
'fill font color(f) and interior color(co) variables based on score selected(s)
Case 1
f = 1
co = 3
Case 2
f = 2
co = 9
Case 3
f = 1
co = 6
Case 4
f = 2
co = 10
Case 5
f = 1
co = 4
End Select
If chkBroken = True Then 'set colors for when this check box is clicked
f = 2
co = 1
End If
If chkpriority = True Then 'set colors for when this box is clicked
f = 6
co = 1
End If
PassData cm, s, co, f, d, v, fov
'pass data to subroutine
End Sub
Sub PassData(ByVal iCam As Integer, iScore As Integer, iColor As Integer, iFont As Integer, iDate As Date, iVisibility As String, iFov)
Dim MySheet As Worksheet
Dim rngFound As Range
Dim sRange As Range
Dim SortRange As Range
Dim lastrow As Long
Dim x As Integer
Dim y As Integer
Set MySheet = ActiveSheet 'sets the current sheet
'First delete match from Broken sheet to camera number
If chkBroken = False Then
With Sheets("Broken")
Set rngFound = Range("A:A").Find(What:=iCam, LookIn:=xlValues, LookAt:=xlWhole)
If Not rngFound Is Nothing Then rngFound.EntireRow.delete
'deletes a broken camera if the camera number matches and is not broken anymore
End With
End If
y = 2
If chkBroken = True Then 'if Broken was selected set score at 0
iScore = 0 'no score for brokens
y = 1
End If
For x = 1 To 3 Step y 'begins a loop to cycle the sheets skips sheet 2 (broken) if broken box was clicked
If iScore = 0 Then iVisibility = "BROKEN" 'changes the text to broken
Sheets(x).Activate
With ActiveSheet
Set sRange = Range(Cells(6, 1), Cells(6, 1).End(xlDown))
Set rngFound = Nothing 'empty the search range result variable
'look in column "A" for iCam (the camera selected) if exists..do the following:
Set rngFound = sRange.Find(What:=iCam, LookIn:=xlValues, _
LookAt:=xlWhole)
If rngFound Is Nothing Then 'if no match then put in bottom row
lastrow = Cells(65536, 1).End(xlUp).Row + 1
Else
lastrow = rngFound.Row 'if match than use the matching camera number row
End If
Cells(lastrow, 1) = iCam * 1
Cells(lastrow, 2) = iScore
Cells(lastrow, 3) = iVisibility
Cells(lastrow, 5) = iDate
Cells(lastrow, 4) = iFov
Range(Cells(lastrow, 1), Cells(lastrow, 5)).Select
With Selection.Font
.ColorIndex = iFont
.Bold = True
End With
Selection.Interior.ColorIndex = iColor
Selection.HorizontalAlignment = xlCenter
If ActiveSheet.Name <> "ByScore" Then
Set SortRange = Range(Cells(6, 1), Cells(6, 1).End(xlDown).Offset(0, 5))
'Sort by Camera number
SortRange.Select
Selection.Sort Key1:=Range("A7"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
If ActiveSheet.Name = "ByScore" Then
Set SortRange = Range(Cells(6, 1), Cells(6, 1).End(xlDown).Offset(0, 5))
'Sort by Camera number
SortRange.Select
Selection.Sort Key1:=Range("B7"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
End With
Next x
For Each ws In Worksheets
ws.Protect
Next ws
MySheet.Activate 'Activate Original Sheet
Range("A7").Select
