I change my input range and my code stops working

[moserz]

I am creating a worksheet for my class that i teach. There are certain cells they type their answers into. The way my code works is on a worksheet_change event. I have defined a range of cells as the ones where the students will type their answers and called it InputRange. The worksheet_change event runs when one of the cells in the InputRange is changed.

BUT, I missed one cell, cell C38, by accident in my InputRange. So I try to add it into my range by going to Insert -> Name -> Define. I click on "InputRange" and I either click on the cell to add it in or I typed it in. Either way, once I add that cell in and click OK, my code seems to stop running entirely.

The code that I have checks students answers and basically says "Correct" or "Try Again" in the cell right next to it. When I add that cell into my InputRange, I get nothing. It stops printing "Correct" and "Try Again" and the color of the cell stops changing and everything basically doesn't work. I don't get an error at all, just no code is running it seems. How do I fix this???

 

[Norie]

Can you post your code?

Are you sure adding to the range has caused the problem?

 

[moserz]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim VRange As Range
  Set VRange = Range("InputRange")
  If Union(Target, VRange).Address = VRange.Address Then
    Select Case Target.Address
      Case "$D$5"
        answerCell = "E5"
        valuesAreEqual = CompareValues(Target.Value, "electron gun", False)
      Case "$D$7"
        answerCell = "E7"
        valuesAreEqual = CompareValues(Target.Value, "negative", False)
      Case "$D$9"
        answerCell = "E9"
        valuesAreEqual = CompareValues(Target.Value, "phosphor", False)
      Case "$D$11"
        answerCell = "E11"
        valuesAreEqual = CompareValues(Target.Value, "", True)
      Case "$D$17"
        answerCell = "E17"
        valuesAreEqual = CompareValues(Target.Value, "", True)
      Case "$D$22"
        answerCell = "E22"
        valuesAreEqual = CompareValues(Target.Value, "", True)
      Case "$D$25"
        answerCell = "E25"
        valuesAreEqual = CompareValues(Target.Value, "", True)
      Case "$D$30"
        answerCell = "E30"
        valuesAreEqual = CompareValues(Target.Value, 13, False)
      Case "$D$34"
        answerCell = "E34"
        valuesAreEqual = CompareValues(Target.Value, 44, False)
      Case "$B$38"
        answerCell = "B39"
        valuesAreEqual = CompareValues(Target.Value, "", True)
      Case "$D$38"
        answerCell = "D39"
        valuesAreEqual = CompareValues(Target.Value, "", True)
      Case "$D$45"
        answerCell = "E45"
        If Not IsNumeric(Evaluate("=" & Target)) Or Target = "" Then
          valuesAreEqual = -1
        Else
          Target.Value = Evaluate(Range("D45").Value)
          valuesAreEqual = CompareValues(Target.Value, 1.706 * 10 ^ 11, False)
        End If
      Case "$D$52"
        answerCell = "E52"
        valuesAreEqual = CompareValues(Target.Value, 3.07, False)
    End Select

    If (valuesAreEqual = 0) Then
      Range(answerCell).Value = "Will be graded later."
      Target.Interior.ColorIndex = xlNone
    ElseIf (valuesAreEqual = 1) Then
      Range(answerCell).Value = "Correct!"
      Target.Interior.ColorIndex = xlNone
    Else
      Range(answerCell).Value = "Try Again."
      Target.Interior.ColorIndex = 36
    End If
  End If
End Sub

Function CompareValues(UserValue, CorrectValue, WillBeGradedLater)
  UserValue = LCase(UserValue)
  If WillBeGradedLater Then
    CompareValues = 0
  ElseIf (InStr(UserValue, CorrectValue) <> 0) Then
    CompareValues = 1
  Else
    CompareValues = -1
  End If
End Function

[Fixed code tags ~ admin]

 

[moserz]

I am not sure that changing the range is what triggers it. That's just what I've noticed. As soon as I change it, it stops working.

 

[Norie]

I'm not quite sure why you are using Union.

If you want to check if Target is in VRange you might be better using Intersect.

Set VRange = Range("InputRange")
If Intersect(Target, VRange) Is Nothing Then Exit Sub

Also since the code actually changes values on the worksheet you should probably be disabling events like this, so it doesn't 'call' itself.

Application.EnableEvents = False

Don't forget to enable events at the end.

Application.EnableEvents = True

Also I don't see the need for AnswerCell.

I think this

 Range(answerCell).Value = "Will be graded later."

could be replaced with this

Target.Offset(,1) = "Will be graded later."

 

[moserz]

Thanks Norrie. I've inputted the Intersect as you had typed it. But it's not quite working yet. I am new at this whole VB stuff, so help me out a little if you can.

1.After the line:

If Intersect(Target, VRange) Is Nothing Then Exit Sub

that you had typed in your response, do I put an Else and then the rest of my code? Where should I put the End If? With all the rest of the End If's?

2. Also, where is the best place to enable and disable the events? At the very beginning and the very end? I'm not sure where to put those.

3. And lastly, sometimes the answercell is the one directly underneath, instead of the one to the right. Is there a short cut way to do that too?

 

[Norie]

1 You don't need and End If.

2 Really depends on personal preference, some recommend you do it right at the start, others just before you actually do the change(s).

3 My bad , I thought you were always dealing with the next column along.

I'm afraid I'm just off now, but if you have further questions post back.

 

[moserz]

Wow! It worked, somehow. Thanks Norie! (sorry about the misspelled name previously) I added my code for C38 and into the InputRange and it worked! I dont' really understand why, but it worked.

Another thing. Because I have specified all the input cells as "text" in the "format cells -> Number Tab category," whenever a number is entered, that little green triangle shows up in the upper left corner telling me that the number is stored as text. Is there a way to get around this so that it doesn't keep showing up? I know I can ignore it, but it only does it for that one time. Any suggestions from anyone on how to always ignore it?