Worksheet change when creating new sheet
 

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

Worksheet change when creating new sheet
 

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

--
Regards,

OssieMac