VBA code fix: let me skip rows i'm not interested in

[jeni007]

Hi. I'm using Excel 2010 to make a grading program for the teachers at my school.

I've set it up so the form teacher (homeroom teacher) enters in the class enrollment information. Then, the other teachers go in one-by-one and enter their subject grades, etc. The form eventually works as a mail merge to print report cards.

The problem I'm running in to has to do with the optional classes. Each level has, for example, "Option 1" and "Option 2", which represent elective classes. Each student MUST take a class for option 1 and option 2, but their classes may be different (one student may be in Japanese while another is taking Computer Studies). While the classes are different, each option can have only one worksheet (or it gets way too confusing and I can't merge all the data at the end to print reports).

So, when a subject teacher (for an elective) goes in and tries to enter grades for his/her class, s/he sees a form. It goes through the students (names have already been entered by their homeroom teacher). This subject teacher should be able to hit the "Skip" button for all students who are not in his/her class and enter information for those who are. With the code below, I can only skip one student and then get stuck. I realize it has something to do with selecting Cell.(iRow,Y) but I'm not really sure how to get around that problem. I'd like the teacher to be able to skip all the students in the class, if necessary (up to 50 students per class).

I'm including the code for the form, as well as for the buttons. The "cmdSkip" (and possibly "cmdSubmit") button I'm guessing will need to be changed.

Argh. And now I'm getting a Runtime Error 424 (Line 30 -- where it defines the iRow) when I try to hit the cmdSkip. I wasn't getting this error before but lost some changes and can't figure out why I'm getting it now.

I thought I was being clear while explaining this, but please let me know if you need any more information.

---------------------------------------------------

Private Sub cmdClearFormInfo_Click()
'clear the data
Me.txtCourseID.Value = ""
Me.txtCourseName.Value = ""
Me.txtTeacherFirst.Value = ""
Me.txtTeacherLast.Value = ""
Me.txtCourseID.SetFocus

End Sub

Private Sub cmdFinish_Click()

'open General Info form
Worksheets("Menu").Select
'hide this form
Me.Hide

End Sub

Private Sub cmdSkip_Click()
'clear the data
Me.txtAchievement.Value = ""
Me.txtEffort.Value = ""
Me.txtComments.Value = ""
Me.txtAttendance.Value = ""
Me.txtConduct.Value = ""

'load next record
'find first empty row in database
iRow = ws.Cells(Rows.Count, 7) _
.End(xlUp).Offset(1, 0).Row
ws.Cell(iRow, 1).Select

'check to make sure it's not the current record showing in the form
If Me.txtFirst.Value = ActiveCell.Offset(0, 2).Value And Me.txtLast.Value = ActiveCell.Offset(0, 1).Value Then
ActiveCell.Offset(1, 0).Select
End If

Me.txtFirst.Value = ActiveCell.Offset(0, 2).Value
Me.txtLast.Value = ActiveCell.Offset(0, 1).Value
Me.txtCourseID.Value = ActiveCell.Offset(-1, 4).Value
Me.txtCourseName.Value = ActiveCell.Offset(-1, 5).Value
Me.txtTeacherFirst.Value = ActiveCell.Offset(-1, 8).Value
Me.txtTeacherLast.Value = ActiveCell.Offset(-1, 7).Value
Me.cmdSkip.SetFocus

End Sub

Private Sub cmdSubmit_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = ActiveSheet

'find first empty row in database
iRow = ws.Cells(Rows.Count, 7) _
.End(xlUp).Offset(1, 0).Row

'check for empty boxes
If Trim(Me.txtTeacherFirst.Value) = "" Then
Me.txtTeacherFirst.SetFocus
MsgBox "Please enter the course teacher's first name."
ElseIf Trim(Me.txtTeacherLast.Value) = "" Then
Me.txtTeacherLast.SetFocus
MsgBox "Please enter the course teacher's last name."
ElseIf Trim(Me.txtFirst.Value) = "" Then
Me.txtFirst.SetFocus
MsgBox "Please enter the student's last name. If you have no more students to enter, click FINISH."
ElseIf Trim(Me.txtLast.Value) = "" Then
Me.txtLast.SetFocus
MsgBox "Please enter the student's last name. If you have no more students to enter, click FINISH."

ElseIf Trim(Me.txtAchievement.Value) = "" Then
Me.txtAchievement.SetFocus
MsgBox "Please enter the marks earned for achievement this term, in a percentage. This number should be between 0-100 and include coursework, internal assessments, exams, and tests. It should be weighted by importance of each assessment criterion. See supporting document MARKS CALCULATOR.XLSM if you need help calculating this number."
ElseIf Trim(Me.txtAchievement.Value) > 100 Then
Me.txtAchievement.SetFocus
MsgBox "Please enter the marks earned for achievement this term, in a percentage. This number should be between 0-100 and include coursework, internal assessments, exams, and tests. It should be weighted by importance of each assessment criterion. See supporting document MARKS CALCULATOR.XLSM if you need help calculating this number."
ElseIf Trim(Me.txtEffort.Value) = "" Then
Me.txtEffort.SetFocus
MsgBox "Please enter the grade for the student's effort this term. The answer should be one of the following: A, B, C, D, E."
ElseIf Trim(Me.txtAttendance.Value) = "" Then
Me.txtAttendance.SetFocus
MsgBox "Please enter the grade for the student's attendance this term. The answer should be one of the following: A, B, C, D, E."
ElseIf Trim(Me.txtConduct.Value) = "" Then
Me.txtConduct.SetFocus
MsgBox "Please enter the grade for the student's conduct this term. The answer should be one of the following: A, B, C, D, E."
ElseIf Trim(Me.txtComments.Value) = "" Then
Me.txtComments.SetFocus
MsgBox "Please enter some comments about the student's performance and attitude this term. This is an important way to communicate with students and parents about the student's strengths, weaknesses, areas of improvement, etc. The more specific you can be, the more helpful this will be. Do not leave this item blank."

Else

'copy the data to the database
ws.Cells(iRow, 2).Value = Me.txtLast.Value
ws.Cells(iRow, 3).Value = Me.txtFirst.Value
ws.Cells(iRow, 5).Value = Me.txtCourseID.Value
ws.Cells(iRow, 6).Value = Me.txtCourseName.Value
ws.Cells(iRow, 8).Value = Me.txtTeacherLast.Value
ws.Cells(iRow, 9).Value = Me.txtTeacherFirst.Value
ws.Cells(iRow, 11).Value = Me.txtAchievement.Value
ws.Cells(iRow, 12).Value = Me.txtEffort.Value
ws.Cells(iRow, 13).Value = Me.txtAttendance.Value
ws.Cells(iRow, 14).Value = Me.txtConduct.Value
ws.Cells(iRow, 15).Value = Me.txtComments.Value

'clear the data
Me.txtAchievement.Value = ""
Me.txtEffort.Value = ""
Me.txtComments.Value = ""
Me.txtAttendance.Value = ""
Me.txtConduct.Value = ""

'load next record
'find first empty row in database
iRow = ws.Cells(Rows.Count, 7) _
.End(xlUp).Offset(1, 0).Row

Me.txtFirst.Value = ws.Cells(iRow, 3).Value
Me.txtLast.Value = ws.Cells(iRow, 2).Value
Me.txtCourseID.Value = ws.Cells(iRow, 5).Offset(-1, 0).Value
Me.txtCourseName.Value = ws.Cells(iRow, 6).Offset(-1, 0).Value
Me.txtTeacherFirst.Value = ws.Cells(iRow, 9).Offset(-1, 0).Value
Me.txtTeacherLast.Value = ws.Cells(iRow, 8).Offset(-1, 0).Value
Me.cmdSkip.SetFocus

Exit Sub
End If
End If


End Sub

Private Sub UserForm_Activate()

Dim iRow As Long
Dim ws As Worksheet
Set ws = ActiveSheet

'find first empty row in database
iRow = ws.Cells(Rows.Count, 7) _
.End(xlUp).Offset(1, 0).Row

'load next record
'find first empty row in database
iRow = ws.Cells(Rows.Count, 7) _
.End(xlUp).Offset(1, 0).Row

'get course info
If ws.Cells(iRow, 5).Value = "" Then
Me.txtCourseID.Value = ws.Range("E2").Value
Else
Me.txtCourseID.Value = ws.Cells(iRow, 5).Value
End If

If ws.Cells(iRow, 6).Value = "" Then
Me.txtCourseName.Value = ws.Range("F2").Value
Else
Me.txtCourseName.Value = ws.Cells(iRow, 6).Value
End If

Me.txtTeacherFirst.Value = ws.Cells(iRow, 9).Offset(-1, 0).Value
Me.txtTeacherLast.Value = ws.Cells(iRow, 8).Offset(-1, 0).Value

'get first student info
If ws.Cells(iRow, 3).Value = "" Then
MsgBox "You are finished with this class! If you feel this is an error, ask the form teacher to check the enrollment for this class."
Else
Me.txtFirst.Value = ws.Cells(iRow, 3).Value
Me.txtLast.Value = ws.Cells(iRow, 2).Value
End If

Me.txtTeacherFirst.SetFocus

Exit Sub
End Sub

 

[AlphaFrog]

...(up to 50 students per class)

That's so sad. That's not a class, that's a demographic.

You may be better off doing all of this in MS-Access. These are just records in a data base. Access is much better suited for handling all of these records. Having said that...


Runtime error problem...

Private Sub cmdSkip_Click()
'clear the data
Me.txtAchievement.Value = ""
Me.txtEffort.Value = ""
Me.txtComments.Value = ""
Me.txtAttendance.Value = ""
Me.txtConduct.Value = ""

'load next record
'find first empty row in database
iRow = [COLOR="Red"]ws[/COLOR].Cells(Rows.Count, 7) _
.End(xlUp).Offset(1, 0).Row
[COLOR="Red"]ws[/COLOR].Cell(iRow, 1).Select

ws is defined in another procedure. This procedure has no idea what ws is. You need to Dim it globally at the very top of the module for it to be shared by all the procedures within the module.


The other issue about skipping I don't quite follow all the details. I understand the problem, but not exactly sure how your data is setup. I would suggest the cmdSkip_Click procedure use the .Find Method to search for the next student with a given "Class" i.e. "Japanese" within the Option1 or Option2, columns.

And lastly...
It would be best if you surround your VBA code with code tags e.g.; [CODE]your VBA code here[/CODE] It makes reading your VBA code much easier. When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign # (a.k.a. hash, hex, octothorp)

 

[jeni007]

Thanks for your reply. I really appreciate the help.

I know what you mean about using Access. I actually spent probably close to 60 hours working on an Access database for this, but got stuck when it came to creating forms. While I'm new to VBA and macros, I've used Excel plenty of times before. The first time I used Access was trying to make this application.

The problem with the "find" feature is that the enrollment (who's taking what class) isn't entered until the subject teacher submits their grades. So, in the data, there are columns like this:

Student Option 1 Opt1 Teacher Opt1 Achievement . . .
Mary S. ENG 301 J. Smith A
Anna V. COM 211 J. Doe C

( . . . is where the other grades (effort, attendance, etc.) go. Then, repeat it all with option 2).

The Japanese, English, Communications, and Agriculture teachers have to actually enter in that their student is enrolled in their course (we're trying to unburden the homeroom teachers with this task).

So, as it stands, the form that the subject teachers use pulls info from the table for each record...
First Name (Student's)
Last Name (Student's)
Form Class (Homeroom)

Then the teacher adds...
Course ID
Teacher's First Name
Teacher's Last Name
Achievement Score
Effort Score
Attendance Score
Conduct Score
Comments

But since each the list may be gone through a couple times for option 1 (once by each of the subject teachers who teach a class for that elective -- e.g., the English teacher, the biology teacher, the computer teacher), teachers need the option to just go to the next record if that student is not in their class. Currently, they have to submit information if they want to go to the next record. I'd like to have them be able to skip records completely if they don't apply. Less chance of them messing up information previously entered by other subject teachers under the same option number.

I'm not sure how to explain this. Please let me know if it's complete rubbish and you can't understand a word of it. I really do appreciate your help and don't want you to waste your time trying to understand my bad explanations.

 

[AlphaFrog]

For the most part, I understand what you are asking.

I'd like to have them be able to skip records completely if they don't apply.

That's the part I don't understand. What's the criteria to determine the next applicable student? Be specific (column letter, data in the column, criteria).

 

[jeni007]

There is not a column that determines the next applicable student. Essentially, I want the teachers to be able to use the form to scroll through each row of data. When they see a student that is in their class (the form shows their names), they can enter and submit the marks for that row. However, if the student shown is not in their class, they can "skip" that person. There is no column that states who is in what class, so I guess Column C, which shows the student's last names) would be the only criteria (Is it blank? No? Okay, then it might be your student."). Other than that, it's up to the subject teacher to decide what students are applicable.

Again, I don't know if that makes any sense. And I'm guessing there are better ways to go about it. If it's impossible to do it this way, I could have the homeroom teachers enter their student's enrollment, which would mean I'd add a column with the subject name for said option (example, we're working on option one. Students must take either Japanese, Biology, or Accounting. Column R will say either "JPN" or "BIO" or "ACC" to indicate which class the student is in). Would that be much easier?

Thanks again!!!