Changing a cell triggers a userform only once, only when cell is changed

[BBrandt]

Hi, I’m relatively new to VBA coding (about 4 weeks in at this point, self taught) and have run into a problem that I just haven’t been able to find a similar enough scenario for yet. I’m trying to run a macro exactly once for every time a cell in a column is manually changed to read “IMMEDIATE”, which is one of four options for all the cells in this column as defined by a data-validation drop down list. I want this to trigger a custom userform (just an error message that I’ve written) the instant the user clicks “IMMEDIATE” from the drop down list.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The trouble now is that once one of the cells in the target column reads “IMMEDIATE”, the userform is displayed whenever any cell in the column is changed, and as I mentioned, I would only like the user form to be triggered if the selected cell has been changed to read immediate.
<o></o>
The code as it is now is as follows:
<o></o>

Sub Worksheet_Change(ByVal Target As Range)<o:p></o:p>
<o:p></o:p>
'Variables<o:p></o:p>
Dim Old_ScrUpdate As Boolean<o:p></o:p>
Dim KeyCells As String<o:p></o:p>
KeyCells = "CD15:CD32"<o:p></o:p>
<o:p></o:p>
'Prevent screen flicker<o:p></o:p>
Old_ScrUpdate = Application.ScreenUpdating<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
<o:p></o:p>
    'If a cell is changed outside of the column in question,<o:p></o:p>
    'do nothing.<o:p></o:p>
    If Intersect(Target, Range("CD15:CD32")) Is Nothing Then<o:p></o:p>
        Exit Sub<o:p></o:p>
    <o:p></o:p>
    'If a cell is changed to read "IMMEDIATE", display a userform.<o:p></o:p>
    Else<o:p></o:p>
    <o:p></o:p>
        If Not Cells.Find("IMMEDIATE", [A1], xlValues, xlWhole, , True) _<o:p></o:p>
        Is Nothing Then<o:p></o:p>
            UserForm1.Show<o:p></o:p>
        End If<o:p></o:p>
        <o:p></o:p>
    End If<o:p></o:p>
<o:p></o:p>
'Turn screen updating back on<o:p></o:p>
Application.ScreenUpdating = Old_ScrUpdate<o:p></o:p>
<o:p></o:p>
End Sub

 

[Richard Schollar]

Hi

You need to run the code within the Worksheet_Change event, not the Selection_Change (this will then fire when the cell is changed, rather than when the selection changes).

 

[BBrandt]

Thanks, I actually noticed that within a few seconds of posting. Typical me. Anyway, the trouble now is that the macro is still triggered if one of the cells in the column reads "IMMEDIATE" and another in the column is changed in any way. For instance, if C15 reads "IMMEDIATE" and C16 is changed to say "N/A", the userform is displayed. Is there a way to change this so that the userform will only be displayed when a cell is newly changed to read "IMMEDIATE", regardless of whether other cells in the column say "IMMEDIATE" or not?

Thanks again, any help is appreciated.

 

[RoryA]

You also only need to check the cells (if more than one) in Target to see if the value is "IMMEDIATE" since the Target argument represents the changed cell(s).

 

[Lewiy]

Try this:

Sub Worksheet_Change(ByVal Target As Range)
Dim Old_ScrUpdate As Boolean
Dim KeyCells As String
KeyCells = "CD15:CD32"
Old_ScrUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
If Not Intersect(Target, Range("CD15:CD32")) Is Nothing Then
    If Target.Value = "IMMEDIATE" Then UserForm1.Show
End If
Application.ScreenUpdating = Old_ScrUpdate
End Sub

It's just your testing for the word that was causing the problem. You only want to check the cell that has changed, rather than see if the word appears in the column already, I presume.

 

[Lewiy]

Also, just as an additional tip re error handling, you may want to add the following lines:

If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
    'rest of code<o:p></o:p>
End If

<o> </o>
Because otherwise the code will error if more than one cell is changed at a time.<o></o>

 

[BBrandt]

Thanks! That solved the problem, and incredibly quickly too (I waited what, 10 minutes after my first post? Amazing, you guys rock.).