[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

Excel VBA- Using Comparison Operators to Prompt Warning Message

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
"Abys" Wallace
Asked:
"Abys" Wallace
  • 2
1 Solution
 
als315Commented:
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
 
redmondbCommented:
... 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
 
"Abys" Wallaceself employedAuthor Commented:
Thank you... works perfectly without needing the format change
0
 
redmondbCommented:
Thanks, abys757.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now