It never crossed my mind for the longest time that there might be the possibility of of adding custom menus to Excel. Either ones that are specific with a particular workbook, or menus that are available for use with any workbook.
When I found out you could have custom menus in Excel, I spent quite a bit of time searching for how to do it. Microsoft’s site wasn’t very clear. A site here and a site there explained parts of the puzzle. Eventually, with allot of experimentation, I got it working.
This tutorial explains how you can create your own custom menus in Excel. The version of Excel I’m using is Excel 2003 SP3.
Custom Menu Modes:
There are two ways you can have custom menus:
- Custom menus that appear only when opening a specific workbook. In this case, we put the VBA code in that specific workbook which is executed in the workbook_open event handler.
- Custom menus that are available in Excel regardless of what workbook is open. In fact, these menus can be available even if no workbook is open! Basically it works the same way as when doing it for a specific workbook. The difference is that we save the file as an Excel Add-In. Then, we go to Add-Ins in Excel, and check our new Add-In. It will be available for use from that point forward.
A Little More Information about Excel Add-Ins:
They are basically Excel workbook files with some exceptions:
- They have a different file extension (*.xla).
- Even though this “workbook” can have one or more tabs, all the tabs are invisible.
- The file must be saved in the directory where the Excel Add-Ins go. Normally, if you pick the add-on file extention type in the Save As dialog, it will point to the correct directory for you.
- Once an Add-Ins has been saved, it still must be picked (checked) from Add-Ins (Tools / Add-Ins menu) in order for it to work.
- You must not have Macros disabled for Add-Ins to work.
- Whenever you check an Add-In in Excel, or if you open Excel and the Add-In is already checked, the workbook_open event code will be executed.
- Whenever you uncheck an Add-In in Excel, or if you close Excel and the Add-In is already checked, the workbook_beforeclose event code will be executed.
Menu Code Overview
Now that we got discussing Add-Ins out of the way, I want to basically give an overview of our code, how it works, etc.:
- Our code for either an Add-In or an individual workbook is basically the same.
- In our workbook_open event handler, we put in code to create our menus.
- In our workbook_beforeclose event handler, we put in code to remove our menus.
- When a menu item in Excel is created, it doesn’t go away, even if you close Excel. This may sound good, but if your menu was workbook specific and the workbook was no longer open you could get an error trying to call a procedure no longer in memory.
- Because of the above, if you aren’t careful, you could end up with multiple menus with the same captions, etc. Basically a mess!
- Because of this, before we create any new menus, it is smart to check to see if there are any old versions of the menus and delete them first! Our sample code will do just that!
- Menus can have keyboard shortcuts. We’ll show you how to do this.
- And of course menu items will have code associated with them which executes when the user clicks on the item. We will show how to do this as well.
- Finally, once you have your menu working nicely, you may want to password protect your module to prevent users from messing with it. We will show you how to do this.
Creating our Sample File
Open Excel, and create a new workbook. You may want to delete the last two tabs since they are not needed, but this is optional.
- Open up the Visual Basic Editor (VBE) from Excel. One way to do this is to select it from the Tools / Macro menu. You could add a VBE button on your tool bar from the View menu sometime too. This makes getting into the editor allot faster going forward.
- From the VBE’s Insert menu, pick the “Module” option.
- I would rename the module to something like: “modMenuTest”
- Press Ctrl-S to save your changes. Do this often as you work on your project!
- This new module will contain the routines we will call to do the following:
- House our menu building and removal routines.
- House our routines which will run when menu items are clicked on.
First Routine, DeleteAMenu:
Add the following code to the new module:
To all you bright programmers out there, getting the SDK working probably seemed to be cake. Not for me. Apple made things confusing. But I’m finally getting somewhere!
Getting the first app example working (which I downloaded off of Apple’s site) was an uphill struggle. I was trying to get the Kalimba Finger Piano working on my iPhone. Downloading the right licence files, putting them in the right places. Not to mention the fact that when ordering the SDK it took me to the Apple store to purchase it and promised a delivery within 5 business days, and that I’d need to be at the ship address to sign for it… Buzzz! Wrong!
I have now downloaded and got running on my iPhone several of the examples that Apple provides. These examples look like they will help me understand how to do different things that I need to understand in order to accomplish my desired goal: A simple game that I have in mind.
Pete from Trails In The Sand: thankyou for your help.
I’ve got a binder set up where I’m keeping all my To-do lists, activity logs, and misc notes for my game. I’m trying to give structure to this thing. I dearly hope this won’t end up being another unfinished project. I’m hoping that the simplicity of the game I want to write will help me in this regards.
The next thing I’m planning to do is assemble one of Pete’s projects and get it working. I think this is the next natural step in progression. It’s one thing to get working a project where all the files have been pre-added, and everything is pre-configured and get it working, it’s another thing to take the piece-parts and do that yourself.
After that, I’m really going to start digging into the code. My to-do list is much more detailed but I think you get the idea.
Tip: When selecting an item in Provisioning, it means more than highlighting it. It means checking the checkbox on the item. This little issue had me wasting probably 2-3 hours of time trying different things.
I’m on the phone with Apple as we speak. I’m trying to find out what has happened to my iPhone Beta SDK. I logged into the Apple Store, checked my order status. The first thing I noticed gratefully is that it did show that I actually did order the SDK, and the my Visa card was charged for it.
What I did not see is any sort of shipping status information at all. The item says under Order Status that it is “Electronically Delivered,” but I saw no place under “Downloads” where I could download it.
Let me back up just a bit… when I ordered it, it seemed to imply that Apple would ship me a package containing the SDK and that I would need to be at the address when the delivery was made so that I could sign for the package. It was implied that the order would come as short as overnight but take up to 5 business days. Maybe I could get it today (6 business days)!
What is mysterious is that there is nothing under my order status to tell me that it shipped, or that it is pending approval before it’s shipped, etc. This is unclear. Hopefully on this call I will find out what’s up.
Right now, I’m on hold whilst the Apple rep tries to find out stuff.
Although I don’t like this, I’m not surprised really. All the things Apple has been doing lately, all the balls they’re trying to keep in the air, I think they’re doing pretty good considering it all… I still want my SDK though! 🙂
As I end this post, I am still on hold…
I got the go-ahead from Apple Development to be in the Beta program for developing for the iPhone. I clicked on the appropriate link, filled in the proper info, whipped out the old credit card, validated everything, read everything and am now waiting to receive my package!
I was pleased to see that I wasn’t just simply given a URL to download stuff from, but that they were going to send me a package containing the goodies I’m looking for. I will have to sign for the package.
I signed up I believe Sunday evening. So I am waiting expectantly for my delivery. I will be able to finally try stuff on my actual phone. How cool is that?
I have managed to get a sample iPhone app compiled and running in the iPhone simulator that they provide. The app is an African thumb piano. It is a very imaginative use of the iPhone. It would be much nicer running on my iPhone rather than a simulator!
There are some really neat and creative sample programs in the iPhone SDK now, some of them besides being good sampls to look at as far as their code are concerned are apps that I’d actually like to have on my iPhone for my own use! I like the idea of the program that turns your iPhone into a level you can use to make sure that your pictures are straight! Cool.
I’ve spent a bit of time looking over the African
Finger Thumb Piano code. I’m still trying to follow where the “start” of the code is. To be honest though, I’ve not spent allot of time looking into this lately.
I received the following email:
In addition to the rich set of resources currently available to you in the iPhone Dev Center, we’ve just added new resources to power your development as you create world-class mobile applications with the iPhone SDK.
Thank you again for applying to the iPhone Developer Program. We’re thrilled with the tremendous response we’ve received. We have many more requests than we can serve during this initial beta period, so we must limit the Program at this time. We plan to expand it during the beta period, and we will contact you regarding your enrollment status at the appropriate time. We appreciate your patience.
I’m not in yet, but at least I’m not out either.
My id for logging in to Apple’s developer web site is different than the id I normally log in to my MacBook with. I was wondering if this was the reason that I could not download the items I subscribed to in the Help program in XCode.
So, I created a user on my MacBook with the same user name and signed in as that and fired up XCode. Next, I brought up the Help, subscribed to the items that I wanted, and, bingo! Those items loaded!
When I tried to compile to the actual iPhone device (as opposed to the simulator), I still get an error… though it is a different error now.
Rats! I went to try it again and I get yet another error: “No Device is Connected” This is in spite of the fact that I can bring up iTunes and it shows that it is connected!
Oh, and in the Organizer, it shows my iPhone. Go figure! It is all too clear that I am still missing some of the pieces of the puzzle.
Due to a frantic, graspy rush to get their greasy hands on the iPhone SDK, the Apple Developer web site not only is slow, now instead of just error messages, the whole server is down. I guess that’s a good problem.
There is that pent up desire out there. Something for Apple to brag about!