I have a Worksheet_Change event that fires whenever the user makes a change to one of 18 cells:
If the user leaves the cell value as blank or not a number, the Target cell is passed to a sub that will fill the cell with a predetermined number. As you can see, if the entry is not a number, a message also displays.
The problem is that if you press the Delete key, the Worksheet_Change event immediately fires (normally you can make all manner of changes to the cell, but it won't fire until you click away), and it displays the MsgBox, and then it errors out in populateSampleInputs.
populateSampleInputs turns off screen updating, then is a series of 18 If-ElseIf statements like this:
The code always errors out on the first If statement; it doesn't matter which cell is the Target. It gives error 13, Type Mismatch.
In my futile attempts at troubleshooting, I can't get boxToPopulate's value to print (or Target's, for that matter). There's something special about the delete key and I don't know what it is. Anyone have an idea? It'll probably be something silly. Thanks for reading.
Code:
If Not Application.Intersect(Target, Union(Range("Trend"), ... Range("Sales"))) Is Nothing Then
If IsEmpty(Target) = True Then
Call populateSampleInputs(Target)
ElseIf IsNumeric(Target) = False Then
MsgBox ("Entry must be a number.")
Call populateSampleInputs(Target)
End If
End If
If the user leaves the cell value as blank or not a number, the Target cell is passed to a sub that will fill the cell with a predetermined number. As you can see, if the entry is not a number, a message also displays.
The problem is that if you press the Delete key, the Worksheet_Change event immediately fires (normally you can make all manner of changes to the cell, but it won't fire until you click away), and it displays the MsgBox, and then it errors out in populateSampleInputs.
populateSampleInputs turns off screen updating, then is a series of 18 If-ElseIf statements like this:
Code:
If boxToPopulate = Sheets("Input").Range("Trend") Then
...
ElseIf boxToPopulate = ...
'boxToPopulate is the Target cell
The code always errors out on the first If statement; it doesn't matter which cell is the Target. It gives error 13, Type Mismatch.
In my futile attempts at troubleshooting, I can't get boxToPopulate's value to print (or Target's, for that matter). There's something special about the delete key and I don't know what it is. Anyone have an idea? It'll probably be something silly. Thanks for reading.