I change my input range and my code stops working

moserz

New Member
Joined
Jul 12, 2006
Messages
20
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???
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Can you post your code?

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

moserz

New Member
Joined
Jul 12, 2006
Messages
20
Code:
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

New Member
Joined
Jul 12, 2006
Messages
20
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
Code:
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.
Code:
Application.EnableEvents = False
Don't forget to enable events at the end.
Code:
Application.EnableEvents = True
Also I don't see the need for AnswerCell.

I think this
Code:
 Range(answerCell).Value = "Will be graded later."
could be replaced with this
Code:
Target.Offset(,1) = "Will be graded later."
 

moserz

New Member
Joined
Jul 12, 2006
Messages
20
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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 :oops:, 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. :coffee:
 

moserz

New Member
Joined
Jul 12, 2006
Messages
20
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?
 

Forum statistics

Threads
1,136,786
Messages
5,677,725
Members
419,716
Latest member
MPunt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top