Determining which workbook(index) is the one closing

  • Thread starter Thread starter Clark Kent
  • Start date Start date
C

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...
 
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
 
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 said:
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 said:
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...
Click to expand...
Click to expand...
 
Beautiful, thanks! I didnt realize there was an "Active Workbook" object.
Should have known... :-)

Gary''s Student said:
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 said:
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...
Click to expand...
Click to expand...
 
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


Rick Rothstein (MVP - VB) said:
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 said:
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 said:
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...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top