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

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


"Clark Kent" wrote:

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

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


"Gary''s Student" wrote in message
...
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


"Clark Kent" wrote:

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

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

"Gary''s Student" wrote:

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


"Clark Kent" wrote:

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