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:
1.
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 .
2.
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 .
 
Search JabSto ::




Custom Search