How to hide excel macro worksheet each time excel starts up?

Question

Hi, so I created a worksheet in excel with common macros that I would like to have available to me every time I use excel. The problem is, now every time I open up an excel file, the worksheet containing the macro will open up as well, and I'll have to hide it manually. How do I keep this macro worksheet from being visible each time I open excel?

Answer 1

Are you sure that you don't mean workbook and not worksheet?



Both of the first two answerers gave you some good data, it's just that neither one really pieced things all together.



1.) Only the really old Excel macros that didn't even run on VBA are actually contained on sheets. If they are the really old macros, there's not really much you can do.



2.) If you are wanting to hide just a worksheet, there are really two or three ways you can do it. Here are the easiest two ways:

a. Select your worksheet then go to Format > Sheet > Hide

b. Press Alt + F11 to open the Visual Basic Explorer window, then in the Project Window in the top left corner click on the worksheet in the workbook you want to hide. Then go down to the Properties window to the Visible property. Set that to xlSheetHidden or xlSheetVeryHidden. The difference between the two is that the one hides the worksheet but if you go to Format > Sheet > Unhide you will see any sheets that have xlSheetHidden. Ones that are set to xlSheetVeryHidden aren't listed and can't be unhidden by the user without going through Visual Basic somehow. If you can't view the Project or Properties Windows go to View > Project Explorer for the Project window or View > Properties Window for the Properties Window.



3.) Before you consider using an Add-In, you might consider using the PERSONAL.XLS file. The PERSONAL.XLS file works just like an Add-In. The PERSONAL.XLS file opens as a hidden file everytime you open Excel as long as the file has been created. If it has been created, you should see a project called PERSONAL.XLS in the Project window. To create the PERSONAL.XLS file, just record a macro and change "Store Macro In" from "This Workbook" to "Personal Macro Workbook" when you record it. Then just put your macros in the PERSONAL.XLS file.



4.) Another way of doing pretty much the same thing as the PERSONAL.XLS file is to do an Add-In. Follow the one answer's instructions on that. No need for me to put the instructions here and make my answer longer than it already is.

Answer 2

Hide Macros In Excel

Answer 3

I'm assuming that you aren't using an ancient version of Excel or XLM macro sheets. What you need to do is create an add-in. If your macros are already in their own workbook, then you are almost there.



All you need to do to create an add-in is to open that workbook and then do as File --> Save As. On the Save As dialog box, change the file type to Microsoft Office Excel Add-in (*.xla) and then save it somewhere that you can find it. Close that file.



Finally, go to Tools --> Add-ins and click the Browse button. Locate your newly created add-in and then click on it and click the OK button. That will load the add-in and it will now be available every time that you open Excel. You can disable it at any time by going back to Tools --> Add-ins and unchecking the box next to your add-in.



The worksheets in the add-in workbook are hidden, so you won't see any visual indication that the add-in is open (unless it creates a menu or toolbar). However, your macros will be available for use by any open workbook.



Tim

http://www.tvmcalcs.com

Answer 4

Assume the sheet you want to hide is Sheet1 of mybook.xls

1. Alt-F11 to open VBA editor, in project explorer(ctrl+r if not showing) and double click on Sheet1(Sheet1) icon under VBAProject(mybook.xls)

2. in properties window(F4 if not showing) below, go to visible that line, click the right change -1 - xlSheetVisible to 1 of the 2 other choice(for hiding sheet)

3. save the worksheet by clicking save icon in vba editor

Hope this helps.

Answer 5

Use the following code to unhide all the sheets in your workbook Sub UnhideSheets() Dim NumOfSheets As Integer Dim x As Integer ThisWorkbook.Activate NumOfSheets = Sheets.Count For x = 1 To NumOfSheets If Sheets(x).Visible = False Then Sheets(x).Visible = True End If Next x End Sub

Answer 6

copy the macro here so we can see it.



chances are somewhere in the macro it says open when excel is opened