Macro loop once or how to change 'worksheet change' to a single instance

[stevantab]

Hi All

I have run into a bit of a problem with my current sub/macro.

What I am trying to do is have a message box pop up to 'warn' the user about a certain condition that appplies. As an example product 1 might have a condition whereas product 2 doesnt have a condition. All products are listed in a dropdown list. The selection of product 1 brings up the message box. The way I have configured the unique identifier of a condition is a logical operation in cell d3 which is =IF(COUNTIF(Sheet2!J:J,TRIM(first_name))>0,1,"") first_name is where the dropdown list is located

so as a flow of the process is the following - user select via drop down list in turn selection triggers the formula in D3. In turn D3 "SHOULD" trigger the msg box only once

While the code I have works, every time the sheet (or another cell beside D3) updates it displays the message box. What I would like the code to do is only display the message when D3 changes AND only in the first instance. I have tried inserting it into the module but can only get it to work in the objects/private sub. The code is the following


Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False


Dim myCell As Range

For Each myCell In Range("D3")

If (Not IsEmpty(myCell)) And myCell.Value <> 17521 And myCell.Value <> "" Then

MsgBox "This Selection has special conditions please check before proceeding.", vbExclamation
Else
Exit Sub

End If
Next myCell

Application.ScreenUpdating = True
End Sub

 

[Micron]

If I understand your issue, then test for the Target.Address which would be the cell that contains the drop down list. D3 is changed by code, which AFAIK isn't detected by a formula doing a calculation (by the Change event - calculation would trigger the Calculate event though). So when you want the event to run, it is the cell that contains the list that is triggering it, not the calculation of D3.

EDIT - if you're saying you only want the event to completely run IF D3 contains a certain calculated value, that is a different thing. Not exactly clear to me.

 

[stevantab]

I am looking for the event/msg box to come up ONLY when D3 value changes, or as the formula says anything above zero. Meaning if the value in D3 is 1 or more triggers the msgbox

 

[stevantab]

....and only on the first change of D3

 

[Micron]

If changing the drop down choice is the only thing that changes the result in D3, then it is as I said. If not, then I'm not sure I can help except to suggest that the Calculate event might be usable IF you test to see if D3 changes. To do that, you'd have to use a sheet module level variable that gets set when the sheet is activated and every time the sheet recalculates. Then in your sheet change event, test if the variable holds the same value as D3. If so, then D3 value wasn't altered so exit sub or avoid doing what you don't want to happen. You could also set a variable of integer type at the same level for a count. Add 1 when D3 updates (old D3 not equal to new D3 value). The code to test for the variable value would also test if the counter is greater than zero.