Negative value Pop-Up warning!

[browncow]

Hi guys, great site, great advice. But I've now got a question I can't find the answer to!

I enter values onto a worksheet names "Entry"

Calculations then occur on various other worksheets.

When I enter a value on the "Entry" worksheet I would like a pop-up warning to occur if a value in boxes B:5 to B:16 on the "ws" worksheet is negative.

Any way of doing this?

Many thanks in advance!

Ian

 

[HalfAce]

Hi Ian, welcome to the board.
Here's a way to do that, and clear the value when the message box is dismissed.
1. Right click on the sheet tab for your "ws" sheet. Choose View code.
2. Copy & paste this code into the white area that is the sheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B5:B16")) Is Nothing Then Exit Sub
If Target.Value < 0 Then MsgBox ("This Value must be a number of zero or greater."), , "Invalid Entry": Target.ClearContents
End Sub

3. Press AltQ to get back to your excel sheet and try it out.
Now, if you just want the warning and not have it clear the value, simply delete the last part of the MsgBox line - : Target.ClearContents

Hope it helps,
Dan

 

[browncow]

I can't get it to work! I have deleted the line about clearing the value. But nothing falshes up. The code section on the WS sheet looks like this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B5:B16")) Is Nothing Then Exit Sub
If Target.Value < 0 Then MsgBox ("Warning: Negative value!."), , "Invalid Entry":
End Sub

 

[HalfAce]

I'm sorry, I totally misread your post. (Not enough coffee yet. )
Try this instead. (In the same place, the sheet code module of the ws sheet.)

Private Sub Worksheet_Calculate()
For Each cell In Range("B5:B16")
    If cell.Value < 0 Then MsgBox ("Warning: Negative value!."), , "Invalid Entry"
Next cell
End Sub

You'll want to delete the other code altogether.

Does this get you any closer?
Dan

 

[browncow]

Is there anyway of stopping the "error screen" from popping up if someone types something stupig (e.g. a letter) in the box?

Works a treat though!

 

[HalfAce]

Two ways you can do that are:
Option 1. Slap this code into the sheet code module for the Entry sheet. (Change the A1:A10 range to whatever range/cell you're making the entries in.)

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub
If Not IsNumeric(Target.Value) Then MsgBox ("This needs to be a numeric value."), , "Invalid Entry": Target.ClearContents
End Sub

Option 2. Select the cell(s) you're making the entries into and from the menu bar, choose Data > Validation > Settings tab. In the Allow field, choose "Whole number" or "Decimal" (or whatever you want to allow), then complete the other (Data, Minimum & Maximum) fields. Then select the Error Alert tab and type in the message you want the user to see when they enter the invalid data. (You can also enter in a Title - whatever you want it so say in the blue stripe at the top of the popup box.) If you like, you can select the Input message tab and enter a (cell comment type) message that appears upon selection of the cell(s), before any entry is made.

I'd likely go with the Data validation method, just cause that's what it's designed for.

Note that both of these methods won't simply show a message if the invalid data is entered (and thus leaving the data entered), these methods will actually prevent the invalid data from being accepted. If all you want is a message that allows the data to remain, post back for something a bit less restrictive.

Hope it helps,
Dan

 

[browncow]

I'm at home now and not at work, but thanks! Your a star!. I'll try it on Monday!

 

[rsizemore75]

Two ways you can do that are:
Option 1. Slap this code into the sheet code module for the Entry sheet. (Change the A1:A10 range to whatever range/cell you're making the entries in.)

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub
If Not IsNumeric(Target.Value) Then MsgBox ("This needs to be a numeric value."), , "Invalid Entry": Target.ClearContents
End Sub

Option 2. Select the cell(s) you're making the entries into and from the menu bar, choose Data > Validation > Settings tab. In the Allow field, choose "Whole number" or "Decimal" (or whatever you want to allow), then complete the other (Data, Minimum & Maximum) fields. Then select the Error Alert tab and type in the message you want the user to see when they enter the invalid data. (You can also enter in a Title - whatever you want it so say in the blue stripe at the top of the popup box.) If you like, you can select the Input message tab and enter a (cell comment type) message that appears upon selection of the cell(s), before any entry is made.

I'd likely go with the Data validation method, just cause that's what it's designed for.

Note that both of these methods won't simply show a message if the invalid data is entered (and thus leaving the data entered), these methods will actually prevent the invalid data from being accepted. If all you want is a message that allows the data to remain, post back for something a bit less restrictive.

Hope it helps,
Dan

Dan,
I would like to know the code you were referring to in your note at the end of this piece.