Creating Popup message

[pdx028]

I need to create a pop up message if a cell value falls within certain parameters.<o></o>
I have found a few VBA codes which are close to what I need, but don’t fulfill all my requirements. I’ve tried to edit them to work for me, but don’t know enough about VBA codes to do it correctly.<o></o>
Here’s what I need it to do..I have cells F11:F20 that I need a pop up message to appear that notifies the user that an extra charge is needed when the values in the individual cells equal 25 or greater. The values entered in the cells are the sum of a formula.<o></o>
If someone can help me the correct code to get this to work that would be great.<o></o>
I also wanted to know if the cell value is 25 or greater, if there is a way to have that connected to another cell. It would only need to enter the qty 1 into cell D51, the quantity doesn’t need to ever be more than 1 regardless how many times the cell value in F11:F20 is greater than 25.<o></o>
Hope this all makes sense, thank you in advance.<o></o>

 

[JLGWhiz]

You can do it with a Worksheet_Change event procedure. Assuming the user is entering data somewhere on that same sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    For i = 11 To 20
        If Cells(i, 6) >= 25 Then
            MsgBox "Additional charge required for Range(" & Cells(i, 6).Address & ")."
            If Range("D51") <> 1 Then Range("D51 ") = 1
        End If
    Next
Application.EnableEvents = True
End Sub

The problem with this procedure is that it will read the same cells for each change on the sheet and will report the same cells over and over so long as they are equal to or greater than 25. The procedure should be copied to the code module of the worksheet on which the range of items to be reported reside. To access the code module, right clike the sheet name tab, then click 'View Code' in the pop up menu. If your workbook is not already macro enabled, you should close the VB Editor and save the workbook as a macro enabled workbook to preserve the code when the workbook is closed.

 

[pdx028]

Thank you!

The code does exactly what I need it to do, but I'm hoping there is a way to fix the problem you mentioned. Once the >=25 is detected, every time you enter any value into any other cell the error message is popping up. It would work much better for me if it only came up once the first time a >=25 value is detected.

I can get the following code to work for a single number, but not once the >= is added to the code. Ive tried to mix parts of the two, but failed to get it to work for me.
http://www.mrexcel.com/forum/excel-questions/421467-if-cell-contents-equals-x-then-msg-box.html

Maybe someone will have better luck than me fixing the problem.

 

[Smitty]

Welcome to the Board!

Try:

If Range("D51") =1 then Exit Sub

At the beginning of the code.

HTH,

 

[pdx028]

I tried putting If Range("D51") =1 then Exit Sub before the begining of the original code. When you try to enter anything into a cell it brings me back to the code and has the error message "Compile Error: Invalid outside procedure"

Is there somewhere else within the code it might work to enter the additional line? I tried with no luck, it either did nothing or stopped working completely.

Thank you!

 

[Smitty]

I should have explained that you needed an else as well. This works for me:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> Range("D51") = 1 <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> i = 11 <SPAN style="color:#00007F">To</SPAN> 20<br>                <SPAN style="color:#00007F">If</SPAN> Cells(i, 6) >= 25 <SPAN style="color:#00007F">Then</SPAN><br>                    MsgBox "Additional charge required for Range(" & Cells(i, 6).Address & ")."<br>                    <SPAN style="color:#00007F">If</SPAN> Range("D51") <> 1 <SPAN style="color:#00007F">Then</SPAN> Range("D51 ") = 1<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[JLGWhiz]

I don't know how you have F11:F20 set up with formulas. That is why I mentioned the effect that the procedure would have. If I knew which cell entries caused the values in F11:F20 to change, I might be able to control the message box a little better.

 

[pdx028]

It works perfectly!

Thank you so much for the help. Really thankful I found this board.