MsgBox when cell value in column exceeds a threshold.

Hi all,

I think there is a simple answer to this one; the user enters data into Column E on Sheet1 and i want my code to display a pop-up box when a cell's value exceeds 500. I've tried the two codes below which i thought would work as Excel didn't highlight any breaks when i wrote the code, but no pop-up box is being generated when values > 500.

Thanks in advance for any suggestions,


Stew




ATTEMPT 1:

Private Sub Threshold_Check2(ByVal Target As range)

Dim cell As range

For Each cell In ActiveSheet.UsedRange.Columns(5).Cells
If cell.Value > 500 Then
MsgBox "Value within 15% of Threshold"
Next cell

End Sub


ATTEMPT 2:


Sub Threshold_Check(ByVal Target As range)


Set Target = range("E1:E150")

For Each cell In range("E1:E150")

If Target.Value > 500# Then
MsgBox "Value within 15% of Threshold"
End If


End Sub

Have you looked into using Excel's built-in Data Validation feature?

I'm a firm believer in not reinventing the wheel, and I think this solution would best meet your needs.

Hi BigBas,

The numbers that are being entered into this spreadsheet are actually being produced from another Macro and the number of rows will be in the 000's. This is eventually going ot be an automated process where the user presses a button and then everything imports and updates automatically. However, if the value goes above a threshold i need the user to be alerted as they generally will not be checking each time they update.

Hope that makes sense.

Sorry, i forgot to mention that when the data is entered, the sheet still needs to record the value but show the message box so that the user is aware. Data validation (i think) will stop the user from entering data above the threshold whereas it still needs to be recorded. I'm going to have a play around though to double check.

Thanks

Understood. Every situation is unique, and it's possible that Data Validation is not right for you. I mentioned it in case you hadn't heard of it.

This raises a couple of new questions. You mention a message box when a value exceeds a threshold, and you subsequently mention an automated process that updates thousands of rows. If not well thought out, a solution could post a headache. For example, do you want a message box for each entry over 500? (Imagine the hassle of thousands of message boxes). Or do you want one message box indicating that a value above 500 was entered? (Now the hassle is finding the entry/entries).

Below I'm adding some code to give you a rough idea of how a macro of this sort might work. In this case, the code checks all of the cells that are selected to see if they exceed 500. Rather than pop up a message box EACH time, it adds to a counter, and at the end of the macro, a message box indicates how many values exceeded 500. Check it out, adapt to your needs, and let me know your thoughts.

Please Login or Register  to view this content.

Another way is too get a message box when a value is entered in Column E.

Please Login or Register  to view this content.