VBA Msgbox in loop.

[BMD44]

Hello All,

I am using IF condition in Excel with a message displayed, but the msg is getting displayed in loop hanging the excel.

Private Sub Worksheet_Change (By Val Target As Range)

If Range ("Status") = "PY"
MsgBoxClick = MsgBox ("Data cannot be submitted".)
End If

End Sub

When the value is PY, it keeps displaying Msg. Even after clicking ok, it keeps displaying msg and I am not able to do any other operation and have to close excel.
Please help me on how can the msg be displayed only once and come out of loop / or go to else condition.

Thanks in advance

 

[DanteAmor]

Do you have any other macro running?

Range("Status")

Does that correspond to a single cell or does "Status" consider multiple cells?

If it is only a cell. Try the following. Replace all your code with the following:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("Status")) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Value = "PY" Then
      MsgBox "Data cannot be submitted"
    End If
  End If
End Sub

 

[BMD44]

Thanks Dante Amor.

I have multiple macros in excel, but during the execution of this macro, I have no other macros running.

Status is one cell value.

I tried your code, but unfortunately it did not work.

I am able to submit data when status is 'PY'. The message did not get displayed.

 

[DanteAmor]

Status is one cell value.

Do you have a formula in that cell?
You can put the formula here.

Or do you manually capture data, is the captured data uppercase "PY"?

 

[BMD44]

The value in cell is through a formula.

it is referring to the value in other sheet.

For eg: Sheet 1(A,2) has 'PY' . That is defined as a range "choice".

This is referred in Sheet2 (A,2) = "=choice"

Sheet 2(A,2) has also a named range which is "Status" which I am using in the code.

 

[DanteAmor]

mmm and on sheet1 in range("A2") do you capture a value or is it another formula?
If you have another formula, it may be more complex to find the origin.

Private Sub Worksheet_Change(ByVal Target As Range)

That event triggered when you modify a cell. A cell that contains a formula does not change the content of the cell, the formula remains the same, what changes is the result of the formula, that is why the Change event is not activated and that is why it does not send the message.
We can try with the Calculate event, try this:

Replace the code with this:

Private Sub Worksheet_Calculate()
  If Range("Status") = "PY" Then
    MsgBox "Data cannot be submitted"
  End If
End Sub

 

[BMD44]

Thanks a lot Amor. It worked.

 

[BMD44]

Hi Amor,

Apologies to bother again.

The code works in the required sheet - sheet 2.

But, I see issue with Sheet 1.

For eg: Sheet 1(A,2) has 'PY' . That is defined as a range "choice".

When sheet 1(A,2) is 'PY', I see 'Data cannot be submitted' msg appearing continuously resulting in getting excel hung.

Can you please suggest,how can we differentiate for multiple sheets

 

[DanteAmor]

mmm and on sheet1 in range("A2") do you capture a value or is it another formula?

That event triggered when you modify a cell. A cell that contains a formula does not change the content of the cell, the formula remains the same, what changes is the result of the formula, that is why the Change event is not activated and that is why it does not send the message.

You have to put here what you have on each sheet in each cell that is involved in the process.
If it is a formula you must put the formula here.
I still think you have other macros running during this process.
I would have to review your file, to review the ENTIRE process.

DROPBOX

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[BMD44]

Hi,

I have 2 cells in 2 sheets.

Sheet1: Dropdown box with values CY,PY - This has a named range = Choice

Now, I am using Range 'Choice' in Sheet 2 and named it as 'Status'

I need a msg to be displayed when 'Status' is PY as 'Data cannot be submitted' in sheet 2.

when I use Private Sub Worksheet_Change (By Val Target As Range) , I see msg coming in loop.

when I use Private Sub Worksheet_Calculate(), it works fine in Sheet2.

But, in sheet 1, I see msg displayed in loop when value is PY.

Please let me know if you need more details