Worksheet change when creating new sheet

[Little Penny]

I using the macro below to create worksheet from a list of names.


Private Function SheetExists(SHName As String, WB As Workbook) As
Boolean
On Error Resume Next
SheetExists = CBool(Len(WB.Worksheets(SHName).Name))
End Function


Sub CreateSheetsFromList()
Dim R As Range
For Each R In Selection.Cells
If R.Text <> vbNullString Then
If SheetExists(R.Text, ThisWorkbook) = False Then
With ThisWorkbook.Worksheets
.Add(after:=.Item(.Count)).Name = R.Text
End With
End If
End If


Next R
End Sub


I would like to have the same Workshet Change event for each sheet
created:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRow As Long
If Target.Column <> 2 Then Exit Sub
If WorksheetFunction.CountIf(Range("B:B"), Target.Value) > 1 Then
MyRow = WorksheetFunction.Match(Target.Value, Range("B:B"), 0)
response = MsgBox("That number has been used on row " _
& MyRow & " Yes to continue NO to cancel", vbYesNo, "Warning")
If response = vbYes Then
Exit Sub
Else
Target.ClearContents
End If
End If
End Sub



How can I have the code add the change event when it creates a new
worksheet?


Thanks

 

[OssieMac]

Hi Little Penny,

You put the Worksheet change event in ThisWorkbook module like following
where the parameter Sh is the worksheet that fired the event. the use with Sh
and palce a dot in front of all the Ranges etc.

Note following code is untested.

Ensure you delete the code from the sheet module.

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

Dim MyRow As Long
With Sh 'Line added
If Target.Column <> 2 Then Exit Sub
If WorksheetFunction.CountIf(.Range("B:B"), Target.Value) > 1 Then
MyRow = WorksheetFunction.Match(Target.Value, .Range("B:B"), 0)
response = MsgBox("That number has been used on row " _
& MyRow & " Yes to continue NO to cancel", vbYesNo, "Warning")
If response = vbYes Then
Exit Sub
Else
Target.ClearContents
End If
End If
End With 'Line added
End Sub