My problem: get a cell to produce a pop up message when it exceeds a
certain value and do not restrict the entry value?
I tried Data - Validation but have had no luck.
The details (much simplified) are these:
I want to multiply B2 x A2 and if the answer exceeds $2499 have a pop up
message appear. Is it possible to have the pop up ocurr while inputting data
into B2 or only after "entering" or when B2 has been reselected.
A B
1 qty unit cost
2 1 $1250

WHAT IS THE METHOD OR FORMULA?

Thanks much for your help. I'm obviously not proficient with this level of
programming.
One last question....your code works but is it possible for the Message Box
to appear without having to select the cell?

" wrote:

Try inserting the following code into your worksheets code module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If ActiveCell.Column = 3 And _
ActiveCell.Value 2499 Then
MsgBox _
"Please make sure that the value here is less than 2,499."
End If

End Sub

This will allow any value but show a message if the value exceeds 2499.
This will only work for column "C" entries. You may change that by
changing the "3" value in the "IF" statement.

Hopefully this will get you started and then you can taylor it to your
needs

I am sure there is a way, you might be able to use the comment
object,or it is going to involve some coding at length. Before I go
and attempt that, please explain why you want this exactly. I mean
what difference does it make in you application whether the msgbox
appears before or after the entry. Is it supposed to be a warning and
nothing more? I need the details if I am going to design something
that you will use. Lemme know....

Mark
First thanks for any assistance.
What I am doing is trying to alert the user (an estimator) that the $ value
entered into the cell exceeds a threshold and that a seperate and certain
type of document is required as backup for audit purposes. The message Box
does that. I am not trying to limit the input as we need it to handle all
values from 1 to infinity (?). The code provided by 2x4s does work but only
when the cell is reselected, not during, or immediately after input which is
what I really desire it to do.

Now I'm going to get greedy. I would also like to prevent any entry into
the cell if another cell called "QTY." does not contain a number 0. Any
thought on this?

"Mark" wrote:

I am sure there is a way, you might be able to use the comment
object,or it is going to involve some coding at length. Before I go
and attempt that, please explain why you want this exactly. I mean
what difference does it make in you application whether the msgbox
appears before or after the entry. Is it supposed to be a warning and
nothing more? I need the details if I am going to design something
that you will use. Lemme know....

Ok, I'm at a library terminal with time constraints. I am going to go
home and put something together. I'm thinking of combining use of the
cells Comment Object (Constant associated Exceeds Message) and Data
validation for the relationship between it's value and "QTY."
Hopefully I'll have something this evening.

Ok, here it is. If you need help or have any problems, just lemme
know...

Option Explicit
'************************************************* *******************
'COPY THE SECTION BELOW INTO THE WORKSHEET THAT HAS YOUR NAMED CELLS
'THEN REMOVE THE '
'************************************************* *******************
'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
' VerifyPricing
'
'End Sub
'************************************************* *******************
'COPY ALL OF THE REST OF THIS INTO A NEW MODULE IN YOUR WORKBOOK
'HOPEFULLY I HAVE GIVEN ENOUGH INFO IN THE COMMENTS FOR YOU TO BE
'ABLE TO MAKE THIS WORK THE WAY YOU WANT.
'************************************************* *******************
Public Const Limit = 2499
'Change this value if you want to alter
'your preset value for an acceptable
'amount in the TARGET(pricing?) cell.

Public Const Minimum = 0
'Change this value to change what minimum
'value Qty must contain in order to allow
'an entry into the TARGET(pricing?) cell.

Public Const BidCell = "AUDIT"
'Change this value in order to Change how
'the TARGET(pricing?) cell is referred to
'in the warning messages.

Public Const MinCell = "QTY"
'Change this value in order to Change how
'the QTY(Minimum?) cell is referred
'to in the warning messages.

Public QTY As Range 'Defined in the VerifyPricing Procedure
'If you want to change the names of
Public AUDIT As Range 'cells on your spreadsheet, you must change
'these as well(within VerifyPricing).
'--------------------------------------------------------------------
Enum WarningTypes

wtExceedMax = 0 'USED BY THE ISSUEWARNING
wtInvalidQty = 1 'PROCEDURE

End Enum

'--------------------------------------------------------------------
' VERIFYPRICING PROCEDURE
'--------------------------------------------------------------------
Sub VerifyPricing()


Set QTY = Range("QTY") 'If you change the name of the
'cell "QTY" in your spreadsheet,
'then you must change the name
'between the quotes in this
'variable. This applies for
'the "TARGET" cell as well.
Set AUDIT = Range("AUDIT")

' PROCEDURE BODY

On Error Resume Next
Select Case AUDIT

Case Is < Limit

AUDIT.Comment.Delete

Case Is Limit

If QTY < 0 Then
IssueWarning AUDIT, wtExceedMax
End If

End Select

Select Case QTY

Case Is = 0

If AUDIT < 0 Then
IssueWarning AUDIT, wtInvalidQty
End If

End Select

End Sub

Sub IssueWarning(Destination As Range, Warning As WarningTypes)

Select Case Warning

Case wtExceedMax

With Destination

.AddComment
.Comment.Visible = True
.Comment.Text Text:="The amount entered " & _
"here exceeds a preset limit. " & _
"For auditing purposes, make sure to file" & _
"form ""XYZ..."""

End With
Beep


Case wtInvalidQty

Destination.ClearContents
MsgBox "The value in " & MinCell & " must be " & _
"greater than " & Minimum & " in " & _
vbCrLf & "order for an entry to be allowed " & _
"in the " & BidCell & " cell!", vbExclamation
QTY.Select

End Select

End Sub

Hi Mark,

My names is Leo, I'm helping Rick out with this issue.
I've got it to work with a single cell/row by assigning the range to only
one cell for each range; QTY and AUDIT.
But when I define the ranges to more than one cell in that column I then
receive the MsgBox for each entry. Further more, somehow, it seems that even
when I populate the cells with correct values, I still receive errors.

My Question: How can I eliminate the MsgBox generating after each
selectionChange on the range?

Thanks.
Leo

"Mark" wrote:

Ok, here it is. If you need help or have any problems, just lemme
know...

Option Explicit
'************************************************* *******************
'COPY THE SECTION BELOW INTO THE WORKSHEET THAT HAS YOUR NAMED CELLS
'THEN REMOVE THE '
'************************************************* *******************
'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
' VerifyPricing
'
'End Sub
'************************************************* *******************
'COPY ALL OF THE REST OF THIS INTO A NEW MODULE IN YOUR WORKBOOK
'HOPEFULLY I HAVE GIVEN ENOUGH INFO IN THE COMMENTS FOR YOU TO BE
'ABLE TO MAKE THIS WORK THE WAY YOU WANT.
'************************************************* *******************
Public Const Limit = 2499
'Change this value if you want to alter
'your preset value for an acceptable
'amount in the TARGET(pricing?) cell.

Public Const Minimum = 0
'Change this value to change what minimum
'value Qty must contain in order to allow
'an entry into the TARGET(pricing?) cell.

Public Const BidCell = "AUDIT"
'Change this value in order to Change how
'the TARGET(pricing?) cell is referred to
'in the warning messages.

Public Const MinCell = "QTY"
'Change this value in order to Change how
'the QTY(Minimum?) cell is referred
'to in the warning messages.

Public QTY As Range 'Defined in the VerifyPricing Procedure
'If you want to change the names of
Public AUDIT As Range 'cells on your spreadsheet, you must change
'these as well(within VerifyPricing).
'--------------------------------------------------------------------
Enum WarningTypes

wtExceedMax = 0 'USED BY THE ISSUEWARNING
wtInvalidQty = 1 'PROCEDURE

End Enum

'--------------------------------------------------------------------
' VERIFYPRICING PROCEDURE
'--------------------------------------------------------------------
Sub VerifyPricing()


Set QTY = Range("QTY") 'If you change the name of the
'cell "QTY" in your spreadsheet,
'then you must change the name
'between the quotes in this
'variable. This applies for
'the "TARGET" cell as well.
Set AUDIT = Range("AUDIT")

' PROCEDURE BODY

On Error Resume Next
Select Case AUDIT

Case Is < Limit

AUDIT.Comment.Delete

Case Is Limit

If QTY < 0 Then
IssueWarning AUDIT, wtExceedMax
End If

End Select

Select Case QTY

Case Is = 0

If AUDIT < 0 Then
IssueWarning AUDIT, wtInvalidQty
End If

End Select

End Sub

Sub IssueWarning(Destination As Range, Warning As WarningTypes)

Select Case Warning

Case wtExceedMax

With Destination

.AddComment
.Comment.Visible = True
.Comment.Text Text:="The amount entered " & _
"here exceeds a preset limit. " & _
"For auditing purposes, make sure to file" & _
"form ""XYZ..."""

End With
Beep


Case wtInvalidQty

Destination.ClearContents
MsgBox "The value in " & MinCell & " must be " & _
"greater than " & Minimum & " in " & _
vbCrLf & "order for an entry to be allowed " & _
"in the " & BidCell & " cell!", vbExclamation
QTY.Select

End Select

End Sub

Well, I did'nt design this to work with more than one cell for each
named Range. If you want to get rid of the MsgBox then just comment
out the Msgbox line in the IssueWarning routine.
Sorry I didn't understand that QTY and AUDIT were supposed to be
something other than a single cell on a worksheet. I tried to work with
many different values in the cells before I sent it to you and I had no
problems. I can only surmise that the reason for the problem is
related to the code recieving conflicting calls for the same Range. I
would be happy to try and refine this to work for your SPECIFIC use,
but you will have to give me an ACCURATE description of your needs.
Other wise I am wasting time building the WRONG TOOL. Lemme know if
you are interested.

Hi Mark,

Thank you for your attention on this issue.
At this time we are going to hold off with further development.

Leo

"Mark" wrote:

Well, I did'nt design this to work with more than one cell for each
named Range. If you want to get rid of the MsgBox then just comment
out the Msgbox line in the IssueWarning routine.
Sorry I didn't understand that QTY and AUDIT were supposed to be
something other than a single cell on a worksheet. I tried to work with
many different values in the cells before I sent it to you and I had no
problems. I can only surmise that the reason for the problem is
related to the code recieving conflicting calls for the same Range. I
would be happy to try and refine this to work for your SPECIFIC use,
but you will have to give me an ACCURATE description of your needs.
Other wise I am wasting time building the WRONG TOOL. Lemme know if
you are interested.

I'm trying to do the same thing in a spreadsheet, but I can't follow these posts. Can anyone help me?