Message box if maximum amount is exceeded

D

delanah

New Member
Joined
May 22, 2006
Messages
21
I wrote the following code to look at each cell in a range and give a message box if the maximum amount is exceeded. The code is looking at each cell in the range, but I get the message box no matter what the amount is. (I get the message for any amount, including zero.) What I need is to change this code so that I only get the message box if the amounts in the range exceed 99,999,999.99. Can anyone tell me where my code needs to be changed?

For Each Cell In MyCell.CurrentRegion.Cells

If Cell.Value > 99999999.99 Then
Msg1 = "Fund exceeds maximum allowed."
Title = "Alert"
Style = vbOKOnly
MsgResponse = MsgBox(Msg1, Style, Title)
If MsgResponse = vbOK Then
Exit Sub
End If
End If
Exit For
Next Cell
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
are the cells formatted in any way as text... if so text is always > a number.

P.S. this works for me as long as there is not text in the range.

Code: 
For Each cell  In MyCell.CurrentRegion.Cells 
    If cell.Value > 99999999.99 Then
        Msg1 = "Fund exceeds maximum allowed."
        Title = "Alert"
        Style = vbOKOnly
        MsgResponse = MsgBox(Msg1, Style, Title)
        If MsgResponse = vbOK Then Exit Sub
    End If
Next cell
Hope this helps.
 
Upvote 0
Thank you for that tip. It is doing the same thing that my original code was doing. It is working in that it is checking each cell. It is still giving me the message box no matter what the value is. I only want the message box if the amount is exceeded. Any other suggestions?
 
Upvote 0
This will work automatically every time the user enters a bad number only, to also work when a formula results in a bad number?
Add the same code to the "Calculate Event"


Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet module code, like: Sheet1, only!
Dim Msg1$, Title$, MsgResponse$
Dim Style

If Application.WorksheetFunction.IsNumber(Target.Value) Then
If Target.Value > 99999999.99 Then
Msg1 = "Fund exceeds maximum allowed."
Title = "Alert"
Style = vbOKOnly

MsgResponse = MsgBox(Msg1, Style, Title)
ActiveSheet.Range(Target.Address).Select
Selection.Value = ""
End If
End If
End Sub
 
Upvote 0
Thank you! That is working much better.

I still have one issue on the worksheet. I only want the code to check the amounts entered in the range M4:M29, instead of the whole worksheet. How can I get that part to work?

This is what I declared for my variables.

Dim msg1 As String
Dim title As String
Dim style As String
Dim msgresponse As String
Dim MyCell As Range
Dim Cell As Variant
Dim WSD As Worksheet
Set WSD = ThisWorkbook.Worksheets("test")
Set MyCell = WSD.Range("M4:M29")
 
Upvote 0
Perhaps by adding a line:

Code: 
If Intersect(Target, MyCell) Is Nothing Then Exit Sub

At the beginning of the code, assuming you are continuing on the event that Joe contributed.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
VBA - Combine Multiple Message Box into a single box
2
Replies
11
Views
574
Joe4
Joe4
B
  • Question
Event code to give message box and change color of auto-shape
Replies
4
Views
156
bh24524
B
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
287
Gajendran Yadhav
G
J
  • Question
Excel VBA Help
2
Replies
15
Views
393
Rick Rothstein
Rick Rothstein
I
  • Solved
Enter row number in listbox1 when value on worksheet found
2
Replies
10
Views
358
ipbr21054
I

Forum statistics

Threads
1,214,935
Messages
6,122,337
Members
449,078
Latest member
skydd

Share this page

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
Back
Top