Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet Change Event - copy cell to another sheet | Excel Discussion (Misc queries) | |||
automatically change the worksheet reference when new sheet added | Excel Worksheet Functions | |||
Q: Creating a macro to sort and group columns in a sheet according to another sheet | Excel Programming | |||
using 2 Worksheet Change Event in a sheet, is it possible? | Excel Programming | |||
Change worksheet formulas from button on chart sheet | Excel Programming |