Using moExcelApp_WorkbookBeforeClose() event

[Jack]

When using:

moExcelApp_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, Cancel As
Boolean)

and there are many opened sheets,

how to check that one particular sheet is beeing closed?

Jack

 

[Dave Peterson]

It gets the wb passed as one of the parms.

You can use wb.name or wb.fullname and check that.

 

[Jack]

That's what I have problem with.
When my vbasic application starts it connects to the opened Excel
spreadsheet
Set moExcelWS = moExcelApp.ActiveWorkbook.ActiveSheet
I know the name of that sheet of course.
moExcelWS.Name gives me that.

Now, how can I compare the the title of that sheet with the workbook in that
moExcelApp_WorkbookBeforeClose() event?
As far as I understand a sheet and a workbook are two different things!
Please explain,
Jack

 

[Dave Peterson]

I didn't understand your question--and I'm confused still.

You want to compare the title (name?) of a worksheet with the name of a
workbook?

 

[Jack]

I need to detect when user closes the sheet, which my application was
connected to.
To detect that I have to use:
moExcelApp_WorkbookBeforeClose() event
haven't I?

And that is the problem I do not know how to deal with.
I know the name of sheet my app was connected to, but the BeforeClose event
is for workbook not for the sheet.
Jack

 

[Dave Peterson]

If you mean deactivate the worksheet? Then use that event.

I need to detect when user closes the sheet, which my application was
connected to.
To detect that I have to use:
moExcelApp_WorkbookBeforeClose() event
haven't I?

And that is the problem I do not know how to deal with.
I know the name of sheet my app was connected to, but the BeforeClose event
is for workbook not for the sheet.
Jack

 

[Jack]

But how to find out that the sheet being closed is the same my app was
connected to?
I feel like repeating myself but you do not understand my problem.
Jack

 

[Dave Peterson]

I don't understand what closing a worksheet means.

I understand that you can close a workbook.
I understand that you can switch to a different worksheet in that same workbook.
I understand that you can switch to a different workbook.
I understand that you can delete a worksheet.
I understand that you can switch to a different application.

But no, I don't understand what close a worksheet means.

But how to find out that the sheet being closed is the same my app was
connected to?
I feel like repeating myself but you do not understand my problem.
Jack

 

[Jack]

OK.
Here is the scenario.
1.
Excel is opened with one sheet: Sheet1.
2.
My app starts up, finds that sheet and connects to it:
Set moExcelWS = moExcelApp.ActiveWorkbook.ActiveSheet
3.
After some time user opens another sheet: Sheet2.
4.
User closes Sheet2.
5.
moExcelApp_WorkbookBeforeClose() event is fired
6.
Now, I want to find out what sheet is being closed at this moment:
is it Sheet1 or Sheet2 ?
WorkbookBeforeClose() event provides information about workbook not the
sheet.

Hope that explains,
Jack

 

[Dave Peterson]

I still don't know what close a sheet means, so I don't have a suggestion.

OK.
Here is the scenario.
1.
Excel is opened with one sheet: Sheet1.
2.
My app starts up, finds that sheet and connects to it:
Set moExcelWS = moExcelApp.ActiveWorkbook.ActiveSheet
3.
After some time user opens another sheet: Sheet2.
4.
User closes Sheet2.
5.
moExcelApp_WorkbookBeforeClose() event is fired
6.
Now, I want to find out what sheet is being closed at this moment:
is it Sheet1 or Sheet2 ?
WorkbookBeforeClose() event provides information about workbook not the
sheet.

Hope that explains,
Jack

 

[Jack]

Imagine Excel opened which contain 2 opened sheets in it.
Each sheet has 'X' available in the top right corner.
If you click on that 'X' it will close that sheet and that is what I am
referring as 'closing a sheet'.
Please note:
You can only close a single sheet when there is more then one sheet opened.
If there is only one sheet the whole Excel app will close.
Jack

 

[Gord Dibben]

Jack

What you really mean is "closing a workbook".

Sheets do not exist by themselves.

Sheets are contained inside workbooks.

You can close a workbook when it is the only workbook open and Excel will not be
forced to close.


Gord Dibben MS Excel MVP

Imagine Excel opened which contain 2 opened sheets in it.
Each sheet has 'X' available in the top right corner.
If you click on that 'X' it will close that sheet and that is what I am
referring as 'closing a sheet'.
Please note:
You can only close a single sheet when there is more then one sheet opened.
If there is only one sheet the whole Excel app will close.
Jack

 

[Dave Peterson]

Are you talking about closing a window?

Are your windows arranged so that you can see more than one worksheet in that
open workbook?

Because it still sounds like you're closing the workbook to me.

Imagine Excel opened which contain 2 opened sheets in it.
Each sheet has 'X' available in the top right corner.
If you click on that 'X' it will close that sheet and that is what I am
referring as 'closing a sheet'.
Please note:
You can only close a single sheet when there is more then one sheet opened.
If there is only one sheet the whole Excel app will close.
Jack

 

[Jack]

I think I start understanding that 'workbook' versus 'sheet' concept but
still have the same question.
How to check which workbook is being closed then?
At the start of my app I check for the open sheet (not workbook).
So I know the title of that sheet.
How can I compare title of that sheet with the workbook title being closed?
It looks like apples and oranges to me.
Other Excel events contain Sh as a parameter and that I understand.
But WorkbookBeforeClose() does NOT contain Sh but Wb.
What should I do?
Jack

Imagine Excel opened which contain 2 opened sheets in it.
Each sheet has 'X' available in the top right corner.
If you click on that 'X' it will close that sheet and that is what I am
referring as 'closing a sheet'.
Please note:
You can only close a single sheet when there is more then one sheet
opened.
If there is only one sheet the whole Excel app will close.
Jack

 

[Jack]

or putting that in another terms:
knowing Sh.Name how to check whether workbook containing Sh.Name is being
closed?
I cannot compare directly: Wb.Name with Sh.Name
What should I compare Wb.Name with?
Jack

Imagine Excel opened which contain 2 opened sheets in it.
Each sheet has 'X' available in the top right corner.
If you click on that 'X' it will close that sheet and that is what I am
referring as 'closing a sheet'.
Please note:
You can only close a single sheet when there is more then one sheet
opened.
If there is only one sheet the whole Excel app will close.
Jack

 

[Dave Peterson]

The workbook that is closing gets passed to that event:

sub moExcelApp_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, _
Cancel As Boolean)
msgbox wb.fullname & " is closing now"

end sub

I'm not sure how you get the name of the worksheet when your routine opens up,
but if you can get the name of the worksheet, you can get the name of that
worksheet's parent (the workbook that contains that worksheet).

dim wks as worksheet
dim wksName as string
dim wkbkName as workbook

set wks = HowEverYouGetThatWorksheet
wksName = wks.name
wkbkname = wks.parent.fullname

The parent of the range is the the worksheet.
The parent of the worksheet is the workbook.
The parent of the workbook is the application (excel itself).

I think I start understanding that 'workbook' versus 'sheet' concept but
still have the same question.
How to check which workbook is being closed then?
At the start of my app I check for the open sheet (not workbook).
So I know the title of that sheet.
How can I compare title of that sheet with the workbook title being closed?
It looks like apples and oranges to me.
Other Excel events contain Sh as a parameter and that I understand.
But WorkbookBeforeClose() does NOT contain Sh but Wb.
What should I do?
Jack

 

[Jack]

Thank you very much.
It took a bit of time to arrive at that but the line:
moExcelWS.Parent.Name
is that what I need.
Last night I tried the line:moExcelWS.Parent but I did not put the .Name
after it.
When I type that in my vbasic IDE it does not give me any more choices after
..Parent and I have stumbled on it.
Thanks,
Jack

The workbook that is closing gets passed to that event:

sub moExcelApp_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, _
Cancel As Boolean)
msgbox wb.fullname & " is closing now"

end sub

I'm not sure how you get the name of the worksheet when your routine opens
up,
but if you can get the name of the worksheet, you can get the name of that
worksheet's parent (the workbook that contains that worksheet).

dim wks as worksheet
dim wksName as string
dim wkbkName as workbook

set wks = HowEverYouGetThatWorksheet
wksName = wks.name
wkbkname = wks.parent.fullname

The parent of the range is the the worksheet.
The parent of the worksheet is the workbook.
The parent of the workbook is the application (excel itself).

 

[Dave Peterson]

..Fullname includes the path.
(just in case you ever need it.)

And if you want to see the properties pop up, you can declare a variable:

dim wkbk as workbook
set wkbk = moExcelWS.parent 'the worksheet's parent

then type
wkbk.
(with that dot)
and you'll see the intellisense help.