VBA script applied to one single cell in all sheets

[CptAckbar]

Hi all. I am new to VBA and I am trying to make multiselection list over big sheet.

My workbook has about 100 sheets. I have also summarization sheet but it is not part of my question.

I have following:
- in every sheet in cell C2 I have a list
- list is defined by name (Formulas -> Defined names) and C2 has defined list (Data validation -> list) by this names and values
- I prepared VBA script to run when selection is done (script take old value, add comma and new value)
- script also secures that new value is not already in the list and makes sorting so values are in alphabetical order

My core code is here (parts can be found on web):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Oldvalue As String
Dim Newvalue As String

Dim WrdArray() As String


Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$C$2" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            WrdArray() = Split(Oldvalue & ", " & Newvalue, ", ")
            BubbleSort WrdArray
            Target.Value = ArrayToDelimited(WrdArray)
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

This works perfectly over one particular sheet (Sheet1 in VBA editor) but if I defined it over the whole workbook (ThisWorkbook in VBA editor) it does not work. I assume that solution will be simple but I am really running out of idea. I am trying to make only one version of code at one place which will work over whole sheets and only at specific C2 cell. I am sure that it will work if I put code to every "sheet related macro" but this will be a lot of work and also there might be some changes in script - very likely - and it will mean change code at 100 places.

I have tried some tests with target.address but I was not successfull. Could you please advice me with this? Thanks a lot.

 

[Fluff]

Hi & welcome to the board.

Try changing your code to a

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

Which goes in ThisWorkbook

 

[mumps]

I think that you may have to add something like this:

Select Case Sh.Name
        Case "Summary"
            Exit Sub
End Select

at the very beginning of the macro so that the "Summary" sheet is excluded. Change the sheet name to suit your needs.

 

[CptAckbar]

Fluff:
Thanks, that's exactly what I ment. Works perfectly.

mumps:
Thanks for your code. I guess it would be the next step in my fight with spreadsheet. Really appreciate your help.

 

[Fluff]

Glad we could help & thanks for the feedback