PDA

View Full Version : Solved: Close Worksheet Macro



kenburenga
12-08-2010, 04:53 PM
I open a blank worksheet (Blank.xlsx) upon launching Excel 2010. This worksheet has all my macros (personal.xlsb).


Sub Auto_Open()
Application.WindowState = xlMaximized
Workbooks.Open Filename:= _
"C:\Users\KLB-Laptop\Documents\My Templates\Blank.xlsx"
ChDir "C:\KLBSheets"
End Sub



If I close the blank worksheet with the CloseSheet macro the worksheet is closed but my macros are still available for the next worksheet I open.

Sub CloseSheet()
Workbooks(2).Close
End Sub



However, if I include Call CloseSheet in my Auto_Open macro the blank sheet is closed but then Book1 is left open.

I want to be able to launch Excel, close all open worksheets, but still have my personal.xlsb macros available for the next worksheet I open

Any clues as to why the Call CloseSheet leaves Book1 open?

Simon Lloyd
12-08-2010, 07:11 PM
You don't need to open a blank workbook to use the personal.xlsb, any macro in there is always available to all open workbooks. Try clicking on the Office Button at the top left of the window. Then at the bottom of the pop-up window, under the recent documents list, there is a button for Excel options.....click that button. Then on the left, click on Add-in's. At the bottom of the Add-in page there is a "Manage" drop down. Select Disabled Items from the "Manage" drop down and hit GO. If Personal.xlsb appears there, enable it again and it will startup when Excel starts up.

kenburenga
12-08-2010, 08:34 PM
Simon -

Personal.xlsb does not appear under the Disabled Options. There are no disabled items. What next?

kenburenga
12-08-2010, 09:05 PM
I modified my Auto_Open macro to the following and created a Personal.xlam file from Personal.xlsb and have it as an Active Application Add-In.

Sub Auto_Open()
Application.WindowState = xlMaximized
ChDir "C:\KLBSheets"
End Sub


When I launch Excel 2010 Book1 shows as the workbook/worksheet. How do I close Book1 in the Auto_Open macro without closing the Personal.xlam file? I have tried closing Book1 with

ThisWorkbook.Close

But this closes Personal.xlsb and Personal.xlam also.

How do I close Book1 without closing the macro files? Or better yet how can I launch Excel 2010 without opening Book1, but with the macros add-in?

kenburenga
12-09-2010, 09:42 AM
Solved. I found that by renaming my PERSONAL.XLSB file in the Excel Xlstart folder to PERSONAL.KLB Excel opens with my macros, but without opening Book1. I stumbled across this amazingly easy fix.