The Easiest Excel Question Ever.

Cleffer · 12-26-2006, 04:03 PM

Excel Office 2003 SP2, v.11.8105

I got a complaint about a user having multiple spreadsheets open and then clicking on the program X to close the one sheet and it closes Excel and *all* the spreadsheets. Of course, there's a small black X to close the sheet just under the program X, but apparently, that's too hard.

Anyone know of a setting anywhere or of a UI tweak that changes this so the red program x just closes the one spreadsheet?

**Glaswegian** · 12-28-2006, 03:43 PM

Hi

There is an easy way that involves VBA code, but my question would then be - "How should the workbook be closed?" Do you have a button that could be used instead?

Cleffer · 01-09-2007, 03:04 PM

Quote:

Originally Posted by Glaswegian

Hi

There is an easy way that involves VBA code, but my question would then be - "How should the workbook be closed?" Do you have a button that could be used instead?

Greetings -

He just wants to close individual spreadsheets by using the program X instead of the little black X, which is located just underneath the program X.

It is apparently easier to reprogram the computer then to reprogram the user.

**Glaswegian** · 01-09-2007, 03:40 PM

LOL...

I asked my question because using code I can disable the closure of Excel and therefore any open workbooks. However, an alternative method of closure is therefore required otherwise you won't be able to close any workbook ever again! The most common way is to put a button on a sheet or other location that can initiate the close.

So, before I provide your closure solution, I'd like to be sure that you are fully aware that you need to arrange a button or alternate means of allowing users to close each workbook they open.

Does that make sense?

Cleffer · 01-10-2007, 06:48 AM

Quote:

Originally Posted by Glaswegian

Does that make sense?

Certainly does.

So, I'm assuming that MS figured that closing Excel was just supposed to work fine this way and did not allow a user access to make this particular change themselves without editing code? And why is that I'm I not suprised?

Functionality. Functionality. Functionality.

**Glaswegian** · 01-10-2007, 01:05 PM

LOL!

OK - a quick demo. Open a new workbook and add this code to ThisWorkbook module:

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
End Sub

Save the workbook with any name and then try and close it with the small x or the large X.

Cleffer · 01-18-2007, 07:12 AM

Worked like a charm.

Add "Man-God" to your self-description.

Many thanks.

12-26-2006, 04:03 PM	#1 (permalink)
Cleffer Registered User Join Date: Dec 2004 Location: Mid-West Posts: 180 OS: 2003/XP/Vista	The Easiest Excel Question Ever. Excel Office 2003 SP2, v.11.8105 I got a complaint about a user having multiple spreadsheets open and then clicking on the program X to close the one sheet and it closes Excel and all the spreadsheets. Of course, there's a small black X to close the sheet just under the program X, but apparently, that's too hard. Anyone know of a setting anywhere or of a UI tweak that changes this so the red program x just closes the one spreadsheet?

12-28-2006, 03:43 PM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,533 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Hi There is an easy way that involves VBA code, but my question would then be - "How should the workbook be closed?" Do you have a button that could be used instead? __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

01-09-2007, 03:40 PM	#4 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,533 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	LOL... I asked my question because using code I can disable the closure of Excel and therefore any open workbooks. However, an alternative method of closure is therefore required otherwise you won't be able to close any workbook ever again! The most common way is to put a button on a sheet or other location that can initiate the close. So, before I provide your closure solution, I'd like to be sure that you are fully aware that you need to arrange a button or alternate means of allowing users to close each workbook they open. Does that make sense? __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

01-10-2007, 01:05 PM	#6 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,533 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	LOL! OK - a quick demo. Open a new workbook and add this code to ThisWorkbook module: Code: Private Sub Workbook_BeforeClose(Cancel As Boolean) Cancel = True End Sub Save the workbook with any name and then try and close it with the small x or the large X. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

01-18-2007, 07:12 AM	#7 (permalink)
Cleffer Registered User Join Date: Dec 2004 Location: Mid-West Posts: 180 OS: 2003/XP/Vista	Worked like a charm. Add "Man-God" to your self-description. Many thanks.