I use the sub below as part of another sub which error checks and saves a grade sheet. There will be at least 39 grade sheets generated throughout the course this is built for. Each event may be accomplished more than once - a weather cancel, or student failed.
The FindEmptyInsertData sub takes the three most important data from a grade sheet (Date, Grade, Status) and adds them to a sheet named Index. Index then keeps a running tally on the events accomplished...based solely on the output of the 'Error Check and Save' macro.
My question is more of a logic question, rather than for specific code (although it'll help). FindEmptyInsertData works wonderfully. However, the data gets pushed and added to Index however many times the user clicks the 'Error Check and Save' Form Control button. I would like it to only get pushed once per grade sheet...the problem/challenge is that a user might need to go back and change the grade sheet (wrong date, different status...etc).
Index looks like this:
Event ABC-1 ABC-2 DEF-1 DEF-2
Date dd-mmm dd-mmm dd-mmm dd-mmm
Grade 1 2 2 3
Status WX EFF EFF EFF
---- ---- ---- ----
Date dd-mmm
Grade 3
Status EFF
I'm thinking that my solution will lie in the fact that only one event will ever be attempted/accomplished per day. Therefore...if date of gradesheet matches the date in index, then don't push the data again...except if the grade or status changes. Ugh, my brain hurts!
Thanks in advance!
Sub FindEmptyInsertData()
Dim ws As Worksheet
Dim gsDate As Date
Dim gsWorking As String
Dim gsMsnNum As String
Dim colNum As Integer
gsWorking = ActiveWindow.ActiveSheet.Name
gsDate = ActiveSheet.Range("S3")
gsGrade = ActiveSheet.Range("D40")
gsStatus = ActiveSheet.Range("O7")
gsMsnNum = ActiveSheet.Range("D3")
Application.ScreenUpdating = False
'Opens up the INDEX (Sheet4) and finds the first empty cell
Sheet4.Activate
Sheet4.Unprotect
'Finds the sortie name column in INDEX
For Each Cell In ActiveSheet.Rows(5).Cells
If Cell = gsMsnNum Then Cell.Select: Exit For
Next Cell
'Takes the active column number and assigns it to the variable
colNum = ActiveCell.Column
'Finds the first open cell in that column
For Each Cell In ActiveSheet.Columns(colNum).Cells
If Len(Cell) = 0 Then Cell.Select: Exit For
Next Cell
ActiveCell.Value = gsDate 'Prints the Date from the GS into the first empty cell
ActiveCell.NumberFormat = "dd-mmm"
Selection.Offset(1, 0).Select 'Moves One Cell Down
ActiveCell.Value = gsGrade 'Prints the Grade from the GS
Selection.Offset(1, 0).Select 'Moves One Cell Down
ActiveCell.Value = gsStatus 'Prints the Status from the GS
ActiveCell.Borders(xlEdgeBottom).Weight = xlMedium 'Adds a bottom border
'Protects the Index Page
Sheet4.Protect
'Returns to the Previously open GS
Worksheets(gsWorking).Activate
End Sub
Without seeing your Index and other code involved, this is the best recommendation I could make.
Conceptually, as far as simple database design goes, you might benefit from adding a 5th row for Event Sequence. Add an integer variable to your code that looks for the sequence and when assigning data for an event. Increment it by 1 when adding event data to the index. If you hate the idea of consuming an entire row you can also tell your code to hide the row.
This way you can have as many entries containing same or different data as necessary. You can choose to accept the "Event" and highest number for "Sequence" as the final submitted grade by default.
Event ABC-1 ABC-2 DEF-1 DEF-2
-------------------------------------
Seq 1 1 1 1 ' <--- Pull data by Event Name & Sequence
Date dd-mmm dd-mmm dd-mmm dd-mmm
Grade 1 2 2 3
Status WX EFF EFF EFF
---- ---- ---- ----
Seq 2 ' <--- Pull data by Event Name & Sequence
Date dd-mmm
Grade 3
Status EFF
Additionally, you could add another row to the Index that would contain data for which event is the active one that you have pushed to the grade sheet.
Event ABC-1 ABC-2 DEF-1 DEF-2
-------------------------------------
Seq 1 1 1 1 ' <--- Pull data by Event Name & Sequence
Date dd-mmm dd-mmm dd-mmm dd-mmm
Grade 1 2 2 3
Status WX EFF EFF EFF
Active 0 1 1 1
---- ---- ---- ----
Seq 2 ' <--- Pull data by Event Name & Sequence
Date dd-mmm
Grade 3
Status EFF
Active 1
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments