title: Converting Worksheet VBA to all Sheets
body: <p>I am trying to take some code is functioning on a single sheet and convert it so that it applies to all worksheets in the book. I thought I could move the code a module and then use the Workbook_SheetChange &quot;wrapper&quot; described in <a href="https://stackoverflow.com/questions/33340448/worksheet-change-event-for-every-sheet-in-the-workbook">Worksheet change event for every sheet in the workbook</a>. However, the Multiple Select dropdown still only works on the original sheet, despite the &quot;Named_Range&quot; being defined on all sheets.</p>
  <pre><code>Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
      With Sh
          Dim OldVal As String
          Dim NewVal As String
      
          ' If more than 1 cell is being changed
          If Target.Count &gt; 1 Then Exit Sub
          If Target.Value = &quot;&quot; Then Exit Sub
          If Not Intersect(Target, ActiveSheet.Range(&quot;Named_Range&quot;)) Is Nothing Then
              ' Turn off events so our changes don't trigger this event again
              Application.EnableEvents = False
              NewVal = Target.Value
              
              ' If there's nothing to undo this will cause an error
              On Error Resume Next
              Application.Undo
              On Error GoTo 0
              OldVal = Target.Value
              ' If selection is already in the cell we want to remove it
              
              If InStr(OldVal, NewVal) Then
                  'If there's a comma in the cell, there's more than one word in the cell
                  If InStr(OldVal, &quot;,&quot;) Then
                      If InStr(OldVal, &quot;, &quot; &amp; NewVal) Then
                          Target.Value = Replace(OldVal, &quot;, &quot; &amp; NewVal, &quot;&quot;)
                      Else
                          Target.Value = Replace(OldVal, NewVal &amp; &quot;, &quot;, &quot;&quot;)
                      End If
                  Else
                      ' If we get to here the selection was the only thing in the cell
                      Target.Value = &quot;&quot;
                  End If
              
              Else
                  If OldVal = &quot;&quot; Then
                      Target.Value = NewVal
                  Else
                      ' Delete cell contents
                      If NewVal = &quot;&quot; Then
                          Target.Value = &quot;&quot;
                      Else
                          ' This IF prevents the same value appearing in the cell multiple times
                          ' If you are happy to have the same value multiple times remove this IF
                          If InStr(Target.Value, NewVal) = 0 Then
                              Target.Value = OldVal &amp; &quot;, &quot; &amp; NewVal
                          End If
                      End If
                  End If
              End If
              Application.EnableEvents = True
                  
          Else
              Exit Sub
          End If
          
      End With
  End Sub
  </code></pre>