Solved: What Happens to Event Programs for Deleted Sheets?

[Cyberdude]

It appears that if I create an event program (Worksheet_Activate) for a worksheet, then I delete that sheet, that the module and event program get deleted as well. I would like a knowledgeable member to comment on that. I have a workbook with a new set of three worksheets every month. They get deleted at the end of the month and new sheets are created. If I started using event macros for those sheets, does anything get messed up after awhile as a result of deleteing those worksheets and having residual event macros hanging around later?

[mvidas]

Hi CD,

If you have code in the sheet object and the sheet is deleted, then the code in that sheet is deleted too. I don't think you'd get errors constantly pasting code into the newly created sheets, but you might be better off using the workbook_sheetactivate event in the ThisWorkbook object, as that code will stay there even if sheets are deleted.[vba]Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Summary" Then
'if you know the name of the sheet...
End If
'or
If Sh.Index = Sheets.Count Then
'if you know what position the sheet will be in...
End If
End Sub[/vba]
Matt

[Cyberdude]

Hi, Matt! I started thinking about it when I was reviewing the Project Explorer display, and noticed that one of the sheets had something like 128 "versions" (or replacements). I'm not real sure what those numeric "subscripts" mean. I started wondering if there was any holdover junk from previous versions, hence my question. The more I think about it, that wasn't 128...it was Sheet128, which means 28 more Sheet1's (or something like that). Anyway I think you have confirmed what I thought, but I feel better having someone else agree with me.
Thanks, Matt.