If, then, else statement, cont

He4Giv
He4Giv used Ask the Experts™
on
The macro below works but it seems the msgbox only appears when I physically run the macro. Is there a way the msgbox will appear automatically as soon as K100 meets the proper condition, OR when the excel file is opened? Also I may open this file 10 times while the first condition is met. Will the msgbox appear each time, or can it be programmed to appear only once for each condition?

If k100 >= 50 And k100 < 75 Then
   MsgBox "50% Complete"
ElseIf k100 >= 75 And k100 < 89 Then
   MsgBox "75% Complete"
ElseIf k100 = 90 Then
   MsgBox "90% Complete"
End If

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can use a method called SetTimer which tells Excel ro run a subroutine like a timer event in VB, say every 100MS.

Here is an example:

http://www.cpearson.com/excel/ontime.htm

Author

Commented:
Wont work....It may be two months before the first condition is met (when Cell K100 reaches 50%) or it could happen in two weeks, or it could take 3 months.  The object of having the message box appear is when that cell does reach any of those three conditions it will let the user know via message box as an extra reminder that when they see it appear they need to send a form letter to sales when the job reaches 50%, 75% and lastly 90% completion. Maybe the macro I showed you needs reworked.
I always thought the msbbox automatically appeared when a condition was met without physically running the macro?
The other problem with this macro is it dont work unless i change the cell to an integer like 50 or 83..Right now the cell is formatted to show percentage. Is there a way for the macro to read the percent value?
Please help thanks
Here's the area I would change:

Private Sub Worksheet_Change(ByVal Target As Range)
    Static lNumTimes As Long
    If lNumTimes = 0 Then

    if lNumTimes = 0 then
       lNumtimes = 1
      If Target.Address = "$K$100" Then
          If k100 >= 50 And k100 < 75 Then
            MsgBox "50% Complete"
          ElseIf k100 >= 75 And k100 < 89 Then
            MsgBox "75% Complete"
          ElseIf k100 = 90 Then
            MsgBox "90% Complete"
          End If
      end if
    End If
End Sub

The static variable says "I've done this already, so don't do it again.  However, if you recalc your sheet, this may not work the way you want it to, so you may want to change to a module-level variable instead.

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I tried the macro but the msgbox wont appear automatically when one of the conditions is met unless i physically run the macro.
I don't have this problem.  When I put that code in there, then type into K100.  Perhaps you have an option set in Excel to disable the macros?

Author

Commented:
I tried what you said....every time i do open a file I enable macros since im used to using them..I dont know how this macro works..I went to K100 and typed in several numbers like 50, 83, etc saved the file closed it and reopened it and nothing...I give up..

Thanks for your time and help
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
- refund and close
Please leave any comments here within the
next seven days.
Per recommendation, points refunded and question closed.

Netminder
EE Admin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial