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-com
ffice
ffice" /><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
>
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
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
The code as it is now is as follows:
<o
Code:
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
Last edited: