Object model won't update after sheet deleted until all VBA code has run

0

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.

Answer
Discuss

Answers

0
Selected Answer

Here's another suggestion which should work (with your macro for exclusions to the update of the table of contents)...

The Workbook_SheetBeforeDelete event seems occurs several times if you delete several sheets simultaneously (once for each sheet deleted as you might expect).

I inserted a blank module and delcared these public variables: 

Public DelNames(1 To 50) As Variant
Public DelCount As Integer
where the 50 sizes an array to capture up to 50 deleted sheets per session, either simultaneously or one-by-one.

Then I added at the workbook level:

Option Base 1

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)

DelCount = DelCount + 1
DelNames(DelCount) = Sh.Name

End Sub
so an initially empty counter DelCount goes to 1 (which is the first index in the array DelNames) and the deleted sheet number is captured each time a sheet is deleted, the counter incrementing to add another array postion.

Then you can recall the deleted sheet names by array position e.g. Debug.Print DelNames(2) in the Intermediate window.

Hope you can integrate that appraoch with your code.

Discuss

Discussion

Thanks so much, John.  I missed this earlier today.
r_guild (rep: 4) Apr 19, '21 at 7:08 pm
Add to Discussion
0

I remember having a similar problem with MS Word a few years back. Trouble is, I dont remember how I resolved it lol: I suspect that I didn't really solve it at all but decided that the promised result wasn't worth the effort. That may or may not be helpful.

I do remember experimenting with different ways of enumeration and think I recall finding a difference between Documents.Count and the number of documents called up with For Each. You may like to try the following.

For f = 1 to Worksheets.Count
    Debug.Print Worksheets(f).Name
Next f

Let me know what you find with that. Perhaps that'll jolt my memory :-)

Discuss
0

Since I haven't seen another solution, I'm going to respond with my own.  Although the object model doesn't update while VBA code is still running, I can get the name of a deleted sheet and screen it out when rebuilding the table of contents as follows:

Public DeletedSheetName As String

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)

  DeletedSheetName = ActiveSheet.Name

End Sub

Sub to Update Table of Links

  For Each ws In ActiveWorkbook.Worksheets

    If ws.Name <> wsLinks.Name And ws.Name <> wsClean.Name And ws.Name <> DeletedSheetName Then

    End If

  Next ws

End Sub

This will only work if a single sheet is deleted. If I select and delete multiple worksheets, the Workbook_SheetBeforeDelete code repeats but only captures the same sheet name over an over.

Discuss

Discussion

Thanks- your approach above triggered another thought- oplease see my revised answer above.
John_Ru (rep: 1072) Apr 19, '21 at 5:48 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login