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

BBrandt

Board Regular
Joined
Jul 14, 2008
Messages
155
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:eek:ffice:eek:ffice" /><o:p></o:p>
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:p></o:p>
The code as it is now is as follows:
<o:p></o:p>
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:

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Board Regular
Joined
Jul 14, 2008
Messages
155
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,115
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284

ADVERTISEMENT

Try this:
Code:
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Also, just as an additional tip re error handling, you may want to add the following lines:
Rich (BB code):
Rich (BB code):
Rich (BB code):
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:p> </o:p>
Because otherwise the code will error if more than one cell is changed at a time.<o:p></o:p>
 

BBrandt

Board Regular
Joined
Jul 14, 2008
Messages
155
Thanks! That solved the problem, and incredibly quickly too (I waited what, 10 minutes after my first post? Amazing, you guys rock.).
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,911
Messages
5,598,819
Members
414,260
Latest member
joishe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top