Update when worksheet close/delete

[Guest]

Hi,

I have a date sturcture hold information about worksheet. I need to update
this data structure when somebody close/delete worksheet.

Thanks,

Samir.

 

[Nigel]

Not sure I understand - what is the Date Structure? Do you mean close the
workbook or delete a worksheet or deactivate the worksheet, delete the
worksheet?

 

[Guest]

Hi,

Actually I have different worksheet and I store each worksheet object in a
list to find out which one is active e.g type A or type B. I need to update
this list when worksheet is delete and remove the worksheet object from the
list. Actually I need worksheet delete event with worksheet name etc. In that
way I can search my worksheet object list and delete the deleted worksheet
object.

Thanks,

Samir.

 

[Nigel]

The workbook sheet activate event is fired when a worksheet is deleted. You
could use that event to scan the workbook sheets and refresh your list.

For example only (put into the workbook codesheet)

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim mySh As Worksheet, myMessage As String

For Each mySh In Sheets

myMessage = myMessage & mySh.Name

If mySh.Name = ActiveSheet.Name Then
myMessage = myMessage & " - Active"
End If

myMessage = myMessage & vbCrLf

Next

MsgBox myMessage

End Sub


--

Regards,
Nigel
(e-mail address removed)