VBA Make pull-down trigger Change Event to put text on another sheet

Drisso in Vegas

New Member
Joined
Aug 23, 2018
Messages
5
Hi All,
Here's one I can't seem to figure out. This code is to effect 2 sheets in this Excel 2016 workbook, "ROW" and "Project Summary". Column D of the ROW sheet has a pull down with a data validated list. One item in the list is "Pending Approval". Column E has the variable string "CIR" which usually repeats across multiple records. The Project Summary sheet has in column A the correlating CIR string but is not repeated, only one record per CIR. What I'm trying to do is when the pull down is changed to "Pending Approval" I want the value in the correlating row on Project Summary in column AA to change to "Update Pending Item", then if the pull down is changed to anything other than "Pending Approval" it should remove that text from Project Summary sheet.
I've found that if I manually add the text on Project Summary this code will remove it when changing the pull down to something else but it doesn't add the text when I change it back. I've been learning VBA for only a few months so I'm hoping I've just overlooked something simple.
BTW, this community ROCKS! I've learning tons from you guys. Thanks!:)



HTML:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As String
If Target.Column = 4 Then
    Application.EnableEvents = False
    If Target.Value = "Pending Approval" Then
        cir = Target.Offset(0, 1).Value
        Worksheets("Project Summary").Activate
        ActiveSheet.Range("A6").Select
        Do Until ActiveCell = ""
            If ActiveCell = cir Then
                ActiveCell.Offset(0, 26).Value = "UPDATE PENDING ITEM"
                Exit Do
            End If
            ActiveCell.Offset(1, 0).Select
        Loop
        Worksheets("ROW").Activate
    End If
    If Target.Value <> "Pending Approval" Then
        cir = Target.Offset(0, 1).Value
        Worksheets("Project Summary").Activate
        ActiveSheet.Range("A6").Select
        Do Until ActiveCell = ""
            If ActiveCell = cir Then
                ActiveCell.Offset(0, 26).Value = ""
                Exit Do
            End If
            ActiveCell.Offset(1, 0).Select
        Loop
        Worksheets("ROW").Activate
    End If
End If
End Sub
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,153
Office Version
  1. 365
Platform
  1. Windows
You disabling events at the start of the code, but not re-enabling them again. Therefore your code will only fire once.
Try adding this line
Code:
        Worksheets("ROW").Activate
    End If
End If
[COLOR=#ff0000]Application.EnableEvents = True[/COLOR]
End Sub
You may also need to run this, just to re-enable events, before the above works.
Code:
Sub chk()
Application.EnableEvents = True
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,153
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,132,685
Messages
5,654,748
Members
418,149
Latest member
amamiche67

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