0

Current problem: I have a dropdown list populated via data validation with static values. The range of this dropdown list is "G2". I am triggering a worksheet change event that was coded in "ThisWorkbook" whenever user makes a selection. However, this event is being triggered every time any cell in that worksheet changes (duh, of course it will). For e.g., towards the end of the code below, I am manipulating a few things and writing it to a different cell range. And because of that, the control is going into an indefinite loop. I am out of ideas. How can I localize the event for dropdown selection only?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sProgramIncrement, sSprintCycle, sSprintStart, sSprintEnd As String
sProgramIncrement = Split(Left(Range("G2"), 7), ".")(0)
sSprintCycle = Split(Left(Range("G2"), 7), ".")(1)
sSprintStart = Split(Right(Range("G2"), 7), "-")(0)
sSprintEnd = Split(Right(Range("G2"), 7), "-")(1)

Select Case Range("G2")
    Case "PI1.S01 J01-J14"
        If sProgramIncrement = "PI1" And Left(sSprintStart, 1) = "J" Then sSprintStartMonth = "01"
        If sProgramIncrement = "PI1" And Left(sSprintEnd, 1) = "J" Then sSprintEndMonth = "01"

        If Left(sSprintStart, 1) = "F" Then sSprintStartMonth = "02"
        If Left(sSprintEnd, 1) = "F" Then sSprintEndMonth = "02"

        If sProgramIncrement = "PI1" And Left(sSprintStart, 1) = "M" Then sSprintStartMonth = "03"

        If sProgramIncrement = "PI2" And sSprintCycle = "S01" And Left(sSprintStart, 1) = "M" Then sSprintStartMonth = "03"

        If sProgramIncrement = "PI2" And Left(sSprintStart, 1) = "A" Then sSprintStartMonth = "04"

        If Left(sSprintStart, 1) = "M" Then sSprintStartMonth = "05"

        If sProgramIncrement = "PI2" And Left(sSprintStart, 1) = "J" Then sSprintStartMonth = "06"

        If sProgramIncrement = "PI3" And Left(sSprintStart, 1) = "J" Then sSprintStartMonth = "07"

        If sProgramIncrement = "PI3" And Left(sSprintStart, 1) = "A" Then sSprintStartMonth = "08"

        If Left(sSprintStart, 1) = "S" Then sSprintStartMonth = "09"
        If Left(sSprintEnd, 1) = "S" Then sSprintEndMonth = "09"

        If Left(sSprintStart, 1) = "O" Then sSprintStartMonth = "10"
        If Left(sSprintEnd, 1) = "O" Then sSprintEndMonth = "10"

        If Left(sSprintStart, 1) = "N" Then sSprintStartMonth = "11"
        If Left(sSprintEnd, 1) = "N" Then sSprintEndMonth = "11"

        If Left(sSprintStart, 1) = "D" Then sSprintStartMonth = "12"
        If Left(sSprintEnd, 1) = "D" Then sSprintEndMonth = "12"

        Worksheets("KPIs").Range("G3") = sSprintStartMonth & "/" & Right(sSprintStart, 2) & "/" & "20"
        Worksheets("KPIs").Range("I3") = sSprintStartMonth & "/" & Right(sSprintEnd, 2) & "/" & "20"


End Select
End Sub

enter image description here

braX
  • 11,506
  • 5
  • 20
  • 33
Jrules80
  • 178
  • 12
  • 1
    Toggle events: `Application.EnableEvents = False` and then `=True` at the end. – BigBen Jan 16 '20 at 03:19
  • 2
    Does this answer your question? [How to end infinite "change" loop in VBA](https://stackoverflow.com/questions/28029283/how-to-end-infinite-change-loop-in-vba) – BigBen Jan 16 '20 at 03:21
  • An [important read](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) while working with `Workbook_SheetChange` or `Worksheet_Change` – Siddharth Rout Jan 16 '20 at 05:37
  • Thank you, Big Ben. It looks like that did the trick. However, I still feel that the process is a bit sluggish. I have a ton of data that is populated dynamically across this specific worksheet (KPIs) and I am afraid that this sub-routine is executed at the drop of a dime, figuratively. I still wish there is a way to just trigger this event when the user selects an item from a drop-down list. – Jrules80 Jan 16 '20 at 14:12
  • @SiddharthRout - Thanks for sharing the article/question. What is the difference between Workbook_SheetChange vs Worksheet_Change? Which is better/recommended over the other? – Jrules80 Jan 16 '20 at 14:15
  • Gimme an hour. In the gym. Will post a detailed comment when I am back. But if you google it you will find lot of links explaining what each of them is for :) if you are still stuck let me know and I will post a comment later as promised. – Siddharth Rout Jan 16 '20 at 15:12

0 Answers0