Microsoft Office Tutorials and References
In Depth Information
Creating an Add-In
plAn AHEAd for BEsT rEsulTs
Any workbook can be converted to an add-in file, but not every workbook after
it is created is a good candidate as an add-in. When I create an add-in, I know in
advance what features I want the add-in to have, and what kind of code to avoid.
This is important, because the add-in file is a hidden workbook that cannot contain
code for activating a sheet or a range of cells.
You are allowed to write data to your add-in file, but you cannot activate the
addin file at any time. If you want to keep any data you’ve written to the add-in file,
you’ll need to save the file in the Workbook_BeforeClose event, because when an
add-in closes, it does not prompt the user to save unsaved changes.
Suppose you want to create an add-in that offers the options to hide, unhide, protect, or unprotect
multiple worksheets. A novice Excel user might perform these tasks one sheet at a time — quite an
undertaking if the workbook contains dozens or hundreds of worksheets, and the tasks are a frequent chore.
In your new workbook that is destined to become an add-in, press the Alt+F11 keys to go to the
Visual Basic Editor. From the VBE menu bar, click Insert ➪ UserForm. If the Properties window is
not visible, press the F4 key. Follow these steps to create the add-in:
Select your new UserForm in its design area. In the Properties window, name the UserForm
frmSheetManager , enter its caption as Sheet Manager , and set its Height property to 210 and
its Width property to 276 .
Place the following controls on your UserForm:
➤ A Label control near the top, setting its Width property to 228 , and its Caption
property to Please select your action .
➤ An OptionButton control below the Label control, keeping the default name
OptionButton1 , setting its BackColor property to white, its Width property to 228 ,
and its Caption property to Unhide all sheets .
➤ A second OptionButton control below OptionButton1 , keeping the default name
OptionButton2 , setting its BackColor property to white, its Width property to 228 ,
and its Caption property to Hide all sheets except active sheet .
➤ A third OptionButton control below OptionButton2 , keeping the default name
OptionButton3 , setting its BackColor property to white, its Width property to 228 ,
and its Caption property to Protect all sheets .
➤ A fourth OptionButton control below OptionButton3 , keeping the default name
OptionButton3 , setting its BackColor property to white, its Width property to 228,
and its Caption property to Unprotect all sheets .