Solved

Excel VBA- Using Comparison Operators to Prompt Warning Message

Posted on 2013-01-13

1 solution

343 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 350 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

Deploy and update a Microsoft Access application in a Citrix environment

Article by: Gustav

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …

MS Excel - using the Hyperlink formula

Article by: Thomas

Do you use a spreadsheet like Microsoft's Excel? Have you ever wanted to link out to a non excel file on your computer or network drive? This is the way I found to do it!

Building Probability Models in Excel Part 6: Investment Modeling Using a Log-Normal Distritribution

Video by: Toby

The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

Using, Creating and Modifying Styles in Microsoft Excel

Video by: Bob

Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…