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)" <(E-Mail Removed)> wrote in
message news:%23YMzoz$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
> message news:99676D47-E510-4A15-AAF0-(E-Mail Removed)...
>> 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...
>
|
Similar Threads
