Determining which workbook(index) is the one closing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Clark Kent - 11 Apr 2008 17:16 GMT

Sorry, if this sounds like dumb question but I'm an excel programming noob.

Basically what I'm trying to do is determine what workbook(index wise) is
the one closing during the "Worbook before Close" method.

For example, lets say I have 5 workbooks open in my Excel session and I
close one of them. I could access them easily by saying
Application.Workbooks[1], etc. However, I just want to know if there is a way
to know which one I'm currently closing.

I can easily count my number of open workbooks using workbook.count but I am
not able to go from that to the actual index of the one I'm closing...

I'm doing this in C# if it helps but I can probably translate VB if someone
knows how to do it...

Reply to this Message

Gary''s Student - 11 Apr 2008 17:30 GMT

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox ("Workbook: " & ActiveWorkbook.Name & " is closing")
i = 1
For Each w In Workbooks
If w.Name = ActiveWorkbook.Name Then
MsgBox ("by the way, i am workbook # : " & i)

End If
i = i + 1
Next
End Sub

Signature

Gary''s Student - gsnu200778

> Sorry, if this sounds like dumb question but I'm an excel programming noob.
>
[quoted text clipped - 11 lines]
> I'm doing this in C# if it helps but I can probably translate VB if someone
> knows how to do it...

Reply to this Message

Rick Rothstein (MVP - VB) - 11 Apr 2008 18:53 GMT

Or, as an alternate, this way...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim X As Long
MsgBox "Workbook: " & ActiveWorkbook.Name & " is closing"
For X = 1 To Workbooks.Count
If Workbooks(X).Name = ActiveWorkbook.Name Then
MsgBox "By the way, I am workbook #" & X
End If
Next
End Sub

Rick

> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> MsgBox ("Workbook: " & ActiveWorkbook.Name & " is closing")
[quoted text clipped - 27 lines]
>> someone
>> knows how to do it...

Reply to this Message

Peter T - 11 Apr 2008 19:42 GMT

But it might not necessarily be the ActiveWorkbook. The event as written
will only fire in the ThisWorkbook module of a workbook, so in the event
could -

Msgbox Me.Name

then loop workbooks to return the index until Me.Name is found (whatever for
though ?)

Regards,
Peter T

> Or, as an alternate, this way...
>
[quoted text clipped - 42 lines]
>>> someone
>>> knows how to do it...

Reply to this Message

Clark Kent - 11 Apr 2008 18:56 GMT

Beautiful, thanks! I didnt realize there was an "Active Workbook" object.
Should have known... :-)

> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> MsgBox ("Workbook: " & ActiveWorkbook.Name & " is closing")
[quoted text clipped - 23 lines]
> > I'm doing this in C# if it helps but I can probably translate VB if someone
> > knows how to do it...

Reply to this Message