Personal.xlsb counts as a workbook, can I "super" hide it?

[Brad E.]

Most of the time when I use macros, they are workbook specific. I put them
in to speed up a repetitive action.

I recently put a macro in my Personal.xlsb file to have it accessible for
all workbooks.

This morning, I opened a workbook which was made to transfer data from an
old workbook to this new one. My "Transfer" button first looked at the
number of Workbooks and if the Count was 2, would check certain cell entries
to be sure the file it was going to be transferring from is of the correct
setup. When I clicked this button this morning, the macro wouldn't run
because Workbooks.Count is now 3, with the third one being Personal.xlsb.

Actually, I am going to expand on my questions with this first post.
1. How can I get Personal.xlsb to not Count as a Workbook, but still have
the macros available?
2. When I go to the VBE, I don't really want to see my Personal macros. Can
I hide those from VBE until I want to add or change the Personal file?

Personal.xlsb is hidden.

 

[Dave Peterson]

#1. Why not just subtract one from the workbooks.count if you know you're
*.xlsb file is included and you don't want it to be?

An alternative...

I've save my personal.xls* file as an addin (.xla or .xlam).

Since it's an addin, it's hidden from the user. And it's not included in the
workbooks.count total.

#2. You can't hide the workbook's project from appearing in the project
explorer (in the VBE), but you can protect it with a password, so you don't see
the code all the time.

Select the personal.xlsb project (in the VBE)
Tools|VBAProject Properties|protection tab

Give it a memorable password.

 

[Brad E.]

Thanks, Dave, for both answers. Before I go ahead and save as an add-in,
which will hide it from the user, I would like to know if that also hides the
macros which will be in the add-in?

 

[Dave Peterson]

You won't be able to see them from the alt-F8 macro dialog.

But you could modify the ribbon/QAT to run those macros.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

Thanks, Dave, for both answers. Before I go ahead and save as an add-in,
which will hide it from the user, I would like to know if that also hides the
macros which will be in the add-in?

 

[Brad E.]

Dave - I looked at the websites you listed in the previous thread and thought
your Add-in adoption looked the most appealing. The problem I am having,
now, is that I am using Excel 2007 and your code is for previous versions.
When I select the Add-Ins from the 2007 Ribbon, I do have a "Custom Toolbars"
box, without any visible buttons. However, when I run my mouse across the
box, there are actually two buttons available (they visually change to show
as highlighted buttons) and resting my mouse does display the correct Tool
Tips.

Do you have any updates to get this to work better in Excel 2007 (Excel 2010)?

 

[Dave Peterson]

No idea about xl2010.

But I use the commandbars in xl2007 and it works fine for me. The toolbars show
up on the Addins tab in the ribbon, though. I'm not sure why you don't see the
buttons on your toolbar. If you modified the code, you'll want to share your
version -- or go back to that source and try it again.

If you don't find that ok, then you'll have to use something else--either
modifying the ribbon or using the QAT. Ron de Bruin shows how to do that.

Dave - I looked at the websites you listed in the previous thread and thought
your Add-in adoption looked the most appealing. The problem I am having,
now, is that I am using Excel 2007 and your code is for previous versions.
When I select the Add-Ins from the 2007 Ribbon, I do have a "Custom Toolbars"
box, without any visible buttons. However, when I run my mouse across the
box, there are actually two buttons available (they visually change to show
as highlighted buttons) and resting my mouse does display the correct Tool
Tips.

Do you have any updates to get this to work better in Excel 2007 (Excel 2010)?

 

[Brad E.]

Thanks, Dave. I did not have the .Style or the .FaceId lines entered because
when I started typing them, they didn't show up as an entry to select. I
think this is why I thought that maybe you had xl2003, also. Anyway, I typed
those in and now everything works great.