How to Addins

excel_Bert

New Member
Joined
Jul 14, 2010
Messages
41
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.

Bert
 
Last edited:

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
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).
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,404
Messages
5,511,157
Members
408,828
Latest member
Csmnvld

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top