Error when closing multiple workbooks using the beforeclose() sub

**alexthapyro** · 07-22-2011, 10:15 AM

The maro that runs in one sheet returns it to a base operating state and clears all the data. This works fine... But The problem is the user may have multiple open windows, and when Excel is exited it tries to close all the windows. If the workbook that contains the beforeclose() sub routine isnt active i get subscript errors. Now it gets better I come in this morning and I am no longer gettting the errors but when I close the sheet it will switch to the workbook containg the subroutine and then you have to exit excel again in order for the workbooks to actually close. How do i fix this so excel will close like normal and not be required to close twice?

This is what i have so far. the before close just call this macro. Mybook and pass code are Dim at the top as strings. Also if these two are declared in this module would the interact with other modules? or other modules in other workbooks?

Please Login or Register  to view this content.

thanks for yalls help

**Whizbang** · 07-22-2011, 10:23 AM

Instead of ActiveWorkbook, use ThisWorkbook.

**alexthapyro** · 07-22-2011, 10:52 AM

If I close out of Excel while editing the other workbook it still leaves both of them open but changes the focus to the workbook that the data is being cleared from. However now it does clear all the data and resets the book how desired. But you still have to exit excel while looking at the workbook that has the beforeclose() script

**alexthapyro** · 07-22-2011, 11:01 AM

Please Login or Register  to view this content.

I thouhgt adding the close workbook would help fix it but now i get a type mismatch when exiting excel when another workbook is active. the error line is in the Windows(mybook).Activate command

**Whizbang** · 07-22-2011, 11:17 AM

You rarely need to activate or select an object to work with it in VBA.

Try this:

Please Login or Register  to view this content.

As far as the error you are getting... Where is the value for mybook set? Is it a public constant? I am guessing that mybook is empty when this macro is run.

**alexthapyro** · 07-22-2011, 11:40 AM

it is defined when the workbook is opened inisde of an auto opening routine. then i used

Please Login or Register  to view this content.

at the top of the module that contains both sub-routines. I have been trying to figure out if mybook would be passed out side of this module, inculding to another open workbook if a variable of the same name was in that book as well.

**alexthapyro** · 07-22-2011, 11:47 AM

I just tried hard wiring the name of the workbook into the windows command. the error then moves from it to the Sheets("welcome").Cells(31, 9) = "" command getting a run time error 9 subscript out of range.

**Whizbang** · 07-22-2011, 12:59 PM

add a period at the beginning.

Please Login or Register  to view this content.

**alexthapyro** · 07-22-2011, 01:36 PM

now im getting object doesnt support this type or property. also is there a way to keep the information in the variable stored so it can use the name of the sheet incase the user renames it?

**Whizbang** · 07-22-2011, 01:54 PM

Sorry. I am such a dope. Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**alexthapyro** · 07-22-2011, 02:05 PM

Well I tried that earlyer and couldnt figure out why it didnt exist and its cause iI left off the s on workbooks. but the annoying part is you still have to have that workbook as active in order to close excel. if you have just that workbook open it all works fine and if you have multiple open but have the workbook that the macro is in selected it works fine.

However if i still have a couple sheets open and it not selected and try to close Excel using the X at the top right it runs the macro cleas the data hides the sheet. But Excel doesnt close you have to exit Excel again, and if for some reason you switch sheets then it will do the same thing.

Im just worried casue in the end there will be multiple sheets open with a similar closing macro so im guessing they will start to fight each other.

**Whizbang** · 07-22-2011, 02:19 PM

Try removing or commenting out this line

Please Login or Register  to view this content.

and see what happens.

**alexthapyro** · 07-22-2011, 02:21 PM

Well now I'm even more of a goober. I changed the wrong macro. It works! So what was the diffence? By making it a with command it effectivly does everything at once and in the bacground no matter wich window is open?

and how do I rate your post and show that it is solved

Error when closing multiple workbooks using the beforeclose() sub

LinkBack

Thread Tools

Rate This Thread

Display

Error when closing multiple workbooks using the beforeclose() sub

Re: Error when closing multiple workbooks using the beforeclose() sub

Re: Error when closing multiple workbooks using the beforeclose() sub

Re: Error when closing multiple workbooks using the beforeclose() sub

Re: Error when closing multiple workbooks using the beforeclose() sub

Re: Error when closing multiple workbooks using the beforeclose() sub

Re: Error when closing multiple workbooks using the beforeclose() sub

Re: Error when closing multiple workbooks using the beforeclose() sub

Re: Error when closing multiple workbooks using the beforeclose() sub

Re: Error when closing multiple workbooks using the beforeclose() sub

Re: Error when closing multiple workbooks using the beforeclose() sub

Re: Error when closing multiple workbooks using the beforeclose() sub

Re: Error when closing multiple workbooks using the beforeclose() sub

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions