Determining which workbook(index) is the one closing

[Clark Kent]

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...

 

[Gary''s Student]

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

 

[Rick Rothstein \(MVP - VB\)]

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")
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

--
Gary''s Student - gsnu200778

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...

 

[Clark Kent]

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")
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

--
Gary''s Student - gsnu200778

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...

 

[Peter T]

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...

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")
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

--
Gary''s Student - gsnu200778

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...