Solved

Excel VBA-  Using Comparison Operators to Prompt Warning Message

Posted on 2013-01-13
4
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
0
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
  • 2
4 Comments
 
LVL 40

Expert Comment

by:als315
ID: 38772417
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
0
 
LVL 26

Accepted Solution

by:
redmondb earned 350 total points
ID: 38772431
... 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.
0
 

Author Closing Comment

by:"Abys" Wallace
ID: 38772688
Thank you... works perfectly without needing the format change
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38772694
Thanks, abys757.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

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 …
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!
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…
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…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question