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>
<?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>
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: