Solved

Excel VBA- Using Comparison Operators to Prompt Warning Message

Posted on 2013-01-13

1 solution

Medium Priority

344 Views

Last Modified: 2013-01-13

Hi Experts,

I have a text box that holds values of adjusted amounts less than $150.

If the amount entered by the end user is over $150 then a warning message advising the end user to check their entry should generate.

What's happening with my current code is that it's not recognizing any amounts under $99.00 so the warning message pops warning that the amount entered needs to be under $150 when it is.

It'll accept some entries like $12 and $15 but if I enter $79 or $9.99 it'll trigger the warning box to pop when the entries are clearly under $150.

I need assistance modifying the code so that it'll work properly.

Thank you in Advance for your time.
Sample-Workbook.xlsm

Comment

Question by:"Abys" Wallace

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

4 Comments

LVL 40

Expert Comment

by:als315

ID: 387724172013-01-13

You are converting number to text before compare. Remove Format and compare value. Something like this:

Private Sub textAdjust_Exit(ByVal Cancel As MSForms.ReturnBoolean)

'Me.textAdjust.Value = Format(Me.textAdjust.Value, "$#,###,###.00")

'checks to see if adj amount is less than $150
If Me.textAdjust.Value > "150" Then
    MsgBox "Adjustment amount should be $150 or Less.  Please check the amount entered."
    Me.textAdjust.Value = ""
End If

End Sub

Open in new window

If you need text in this field, convert it after IF

LVL 26

Accepted Solution

by:redmondb

redmondb earned 1400 total points

ID: 387724312013-01-13

... alternatively, (edit: without needing a format change)...

Private Sub textAdjust_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Me.textAdjust.Value = Format(Me.textAdjust.Value, "$#,###,###.00")

'checks to see if adj amount is less than $150
If CDbl(Replace(Me.textAdjust.Value, "$", "")) > 150 Then
    MsgBox "Adjustment amount should be $150 or Less.  Please check the amount entered."
    Me.textAdjust.Value = ""
End If

End Sub

Open in new window

Regards,
Brian.

Author Closing Comment

by:"Abys" Wallace

ID: 387726882013-01-13

Thank you... works perfectly without needing the format change

LVL 26

Expert Comment

by:redmondb

ID: 387726942013-01-13

Thanks, abys757.

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Automated Link Checker

Article by: Josh

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…