I use the event driven procedure "Workbook_SheetBeforeDelete" to flag when a worksheet has ben deleted. Since each sheet is a "chapter in a book" I want to rebuild the "table of contents" to reflect the deletion of a "chapter".
However, the Excel object model does not refresh until all event driven code has run.
Sequence of events following user's deletion of a sheet.
Private Sub Workbook_SheetBeforeDelete
DeletedSheetName = ActiveSheet.Name
End sub
Private Sub Workbook_SheetDeactivate
If Len(DeletedSheetName) > 0 Then
wsLinks.Activate '"Table of Contents sheet"
End If
End Sub
Private Sub Worksheet_Activate '"For wsLinks"
If Len(DeletedSheetName) > 0 Then
Mod_Create_Links.BeginUpdate
End Sub
Mod_Create_Links.BeginUpdate
For Each ws In ActiveWorkbook.Worksheets
' EXCEL's object model still includes the deleted sheet
Next ws
End Sub
Is there a VBA instruction I can execute to update the Excel object model
while this code is still running?
Note: I have a workaround using DeletedSheetName to exclude the deleted
sheet from being entered in the new table of contents but am looking for a more elegant way to keep the code simple.