Microsoft Office Tutorials and References
In Depth Information
Changing the Add-In’s Code
Private Sub Workbook_BeforeClose(Cancel As Boolean)
‘Delete the custom menu item from the Tools menu.
‘The error bypass is for cases when the “Sheet Manager”
‘item is not listed on the Tools menu.
On Error Resume Next
Application.CommandBars(“Worksheet Menu Bar”) _
cHAnging THE Add-in’s codE
You’ll find that some of your add-ins are a work in progress. Users will enjoy the ease of performing
add-in tasks, and you’ll be requested to make enhancements to the add-in for more functionality. As
you pick up more VBA programming skills, you’ll want to improve your original code by making
edits for speed and efficiency.
Any changes you make to your add-in file will be done in the Visual Basic Editor. Open your add-in
file and all you will see is an empty-looking Excel file because all the sheets in an add-in are hidden
and cannot be viewed. Press Alt+F11 to go to the VBE, and just as if it were any Excel workbook,
make whatever changes to the code you need to make. When you are done, save your changes in the
VBE and close the add-in file.
For add-ins that you distribute to other users, you’ll want to protect the code
from being inadvertently changed or viewed by others. The process for
protecting your add-in code is the same as with any Excel workbook, and that is to lock
and protect the project in the Visual Basic Editor. The steps to do this are
discussed in Lesson 4, in the section “Locking and Protecting the VBE.”
As you saw in the section “Changing the Add-in’s Code,” you can open an add-in file, but you might
like to know how to close an add-in file, because it cannot be closed the same way you close a
workbook. You have three ways to close an add-in file:
Deselect (uncheck) the add-in’s name in the Add-Ins dialog box.
Go into the VBE and press Ctrl+G to ensure that the Immediate window is open. In the
Immediate window, enter a line of code that closes the add-in file and press Enter. An
example of such code for the SheetManager add-in is as follows:
Close Excel, which closes all files, including add-ins.