If Greater number msgbox

[TOM R]

Hello Members, I been trying to code a macro below without luck. What I like to do is if cell H3 is Greater than cell A35 to promt the message box below. But if less than do nothing. And if possible if less than to look at sheet 2 cell A35 and do the same.

If WorksheetFunction.Sum(Range("H3") >= ("A35")) Then Exit Sub
MsgBox "QTY. MUST BE GREATER THAN END FEQUENCY", vbOKOnly

Regards, Tom

 

[Norie]

Tom


Why are you using WorksheetFunction.Sum?

You aren't summing anything.

 

[bobinnc]

Tom,

Depending on which way you want it to react:

Why not use Data Validation in cell H3?
Specify the following. Allow: Decimal, Data: "less than", Maximum: =$A$35

- or -

Why not use Data Validation in cell A35?
Specify the following. Allow: Decimal, Data: "greater than", Minimum: =$H$3

 

[TOM R]

Hi Norie, I"m not sure what to use, I get the message to prompt. but its not right.


Regards, Tom

 

[TOM R]

Hi Bob, Thanks for your reply. I can't use Data Valadation since the number in A35 will change depending on a fequency input. if it where a known input I would use this.


Regards Tom

 

[Tom Urtis]

How does the number in cell H3 get there...is it manually entered or is there a formula in cell H3 that produces the number.

And why do you say this
"if cell H3 is Greater than cell A35"

but you code this
> = ("A35"))
which means greater than or equal to


Also this you wrote...
"And if possible if less than to look at sheet 2 cell A35 and do the same."
...no idea what that means.

 

[bdfagoodfella]

I'm sure there are more elegant solutions out there but this worked for me.

I added this code to the worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("h3") >= Range("a35") Then
Exit Sub
Else
MsgBox "Warning message here."
End If
End Sub

(This code will fire every time a user selects a new cell in the worksheet.)

 

[TOM R]

Hello Tom, Thank you for your response.

The number is entered Manually into cell H3, Also, The equals sign does not have to be in there.

For the last part of my question was or is Sheets 1 thru Sheet 5 are the same. Except for cells A35 this is a frequency number that is calculated but always in ascending order thru the sheets. So if say cell H3 input is 200 and in cells A35 may be 40 then sheets it might take up to 5 sheets to be greater than cell H3. I'm not to concerned if its to big of a problem to try and figure out. the last part but if possible I like to see if can be done.


Regards Tom

 

[Tom Urtis]

Just for starters then, right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$H$3" Then Exit Sub
If Range("H3").Value >= Range("A35").Value Then MsgBox "QTY. MUST BE GREATER THAN END FEQUENCY", , "FYI"
End Sub

 

[TOM R]

Hi Tom and bdfagoodfella, Your code works to a tee. I dont know if you would want to try and persue the later but if not this will surfice.


Regards Tom