How to Addins


New Member
Jul 14, 2010
I'm making a Book that by clicking a button creates all the necessary pages for the user, the catch is that I need VBA event code to be present on each newly created page and also on the modules. I don't know if there's a programmatically way to do all that, though. To load the code on the back of each page, to insert modules and to load the corresponding code on them.
Since I don't want the users to mess with the code I started reading a bit about addins, and I am wondering about it.
Can they detect events on the pages and get programs started?
Do they also work like modules?
How can I get started working on them?
Do I write the code on a notepad and then load it into Excel?

Can anyone help me out with this?
Really appreciate the help.

Last edited:

Excel Facts

Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

PA HS Teacher

Well-known Member
Jul 17, 2004
If the desired code is the same or virtually the same for each 'page', you might want to try a custom class like the following. Insert a class module. In this case I named the class module MyClass.

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">WithEvents</SPAN> WS <SPAN style="color:#00007F">As</SPAN> Worksheet<br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> WS_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>  MsgBox "Hi, you just made a change in cell " & Target.Address & " on sheet " & WS.Name<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Then, in a regular module, you need a routine to add pages, and set them equal to a worksheet object in the custom class. They will then contain all of the Worksheet Events you've written into your custom class.

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> SpecialCol <SPAN style="color:#00007F">As</SPAN> Collection<br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Make3SpecialSheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> WSht <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> SpecialWS <SPAN style="color:#00007F">As</SPAN> MyClass<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> SpecialCol <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Set</SPAN> SpecialCol = <SPAN style="color:#00007F">New</SPAN> Collection<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 3<br>        <SPAN style="color:#00007F">Set</SPAN> WSht = ThisWorkbook.Worksheets.Add<br>        <SPAN style="color:#00007F">Set</SPAN> SpecialWS = <SPAN style="color:#00007F">New</SPAN> MyClass<br>        <SPAN style="color:#00007F">Set</SPAN> SpecialWS.WS = WSht<br>        SpecialCol.Add SpecialWS<br>    <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#007F00">' You will also need some code in the workbook open event to loop through the pages already in the</SPAN><br><SPAN style="color:#007F00">' workbook, decide which existing pages you want to have the SpecialWs.WS events, and add them to the</SPAN><br><SPAN style="color:#007F00">' SpecialCol collection</SPAN></FONT>

I didn't write the code, but you'll want a routine that runs perhaps when the workbook opens, to decide which existing pages you want to have the MyClass.WS events, and add them to the SpecialCol collection. Otherwise you will only have MyClass.WS events for worksheets that were just added using the above routine.


MrExcel MVP
Mar 2, 2007
Office Version
  1. 2019
  1. Windows
Some other notes: Addins are not much different than regular workbooks but they are hidden by default, and their functions are made available to other workbooks (at least as User-Defined worksheet functions go). The code isn't much safer than anything else in Excel VBA though. There is a little bit of help for the user to load/unload addins via the Excel GUI - which is nice.

Most Sheet events have a counterpart in the ThisWorkbook module (class) - so for instance, sheets have a Change event, but in the ThisWorkbook module you have a Worksheet_Change event, and so on.

As noted above it really isn't very hard to create an application class with events, either. I personally write all my Excel vba code in the Excel visual basic editor just for the convenience of intellisense and easy compilation (which is good for debugging your code as you go).

Watch MrExcel Video

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back