If-Else-Range-Message Box Help

[Kandiber]

Hi friends! I have just started learn visual basic and need your help if you can.

I have a worksheet in which the user enter monthly sales to range E10:E46 row by row monthly. According to each sales number entered to this range, other cells in the same row have formulas and automatically calculate what I need to know basing on the sales number.

I want excel to recognize each sales number when it is entered to the new cell in E10:E46. After this newly entered number, if the cumulative sum of the range from E10 to the lastly entered number (I mean E48) is bigger than the number in B3, it should warn me with a message box "Attention to red cells" sentence and let me make some other calculations after clicking OK button. If the number in E48 is equal or lower than the number in B3, it should give me a message box "No problem!" sentence and the message box should be closed after clicking the OK button.

By myself, I wrote the code below for this purpose :

Private Sub Worksheet_Change(ByVal Target As Range)
Range("e48").Formula = "=Sum(e10:e46)"
If Range("e48") > Range("b3") Then
MsgBox "Attention to red cells!", vbOK
Else
MsgBox "No problem!", vbOK
End If
End Sub

However, the problem is now the message boxes never closed. After clickin OK button or close button, it always renews the message box related to the if statement. I think I should add a code which can stop " if " after the clicking the OK button and works again after the new number entered into the following cell.

Can you please help?

Sincerely.

[si_the_geek]

Welcome to VBForums

Thread moved to the 'Office Development/VBA' forum... note that while it certainly isn't made clear, the "VB Editor" in Office programs is actually VBA rather than VB, so the 'VB6' forum is not really apt

[si_the_geek]

The following line of your code changes the worksheet:

    Range("e48").Formula = "=Sum(e10:e46)"

...and changing the worksheet automatically calls the Sub again.

To avoid that, either remove the line entirely (set the cell value manually, but you need to trust the users not to change it), or only set the value if it is different, eg:

    If Range("e48").Formula <> "=Sum(e10:e46)" Then Range("e48").Formula = "=Sum(e10:e46)"

[Kandiber]

Dear si_the_geek, according to your response, I changed

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("e48") > Range("B3") Then
MsgBox "Attention to red cells!"
Else
MsgBox "No problem"
End If
End Sub


Now it works as I wanted and hope will not be problematic anymore.

Thank you very much for your response.

[westconn1]

you can also set application.enableevents = false at the start of the sub, then any changes to cells within the sub will not fire the event again

remember to set enableevents = true at the end of the procedure

[Kandiber]

Dear westconn1, thank you for response. Now the code works but it checks any change in any cell in the sheet and gives the message boxes as I want. However, how can fix it in order to get message boxes according to changes in the cells of E10:E46 range. Thanks...

[westconn1]

try like

if not intersect(target, range("e10:e46")) is nothing then

i just typed this in the browser, so may contain error