Code in 'ThisWorkbook' module in add-in?

**karan** · 04-29-2009, 12:20 PM

Hi All,

I have made a macro that displays a textbox containing the contents of a cell once the cell is clicked. The macro is written in the 'ThisWorkbook' module in the Sub 'Worksheet_SelectionChange'.

Now I want to convert this macro into an add-in so that it works on all the Excel workbooks I open. Is it possible to do this? Or is there a way in which we can automatically put the code in the 'ThisWorkbook' module of any open worksheet (doesn't seem feasible).

If you have any ideas, please let me know?

This query was posted yesterday by Vaibhav. Please find it here.

Regards,
Karan

**shg** · 04-29-2009, 01:15 PM

Or is there a way in which we can automatically put the code in the 'ThisWorkbook' module of any open worksheet (doesn't seem feasible).

It is, and there was a recent example on the forum, but I don't think you want to do that. I think you want to put it in a class module in the add-in:

Please Login or Register  to view this content.

See http://www.cpearson.com/excel/Events.aspx

**c.vaibhav** · 04-29-2009, 02:04 PM

Wow!! It's amazing..

I din't knew you one can do so much with the use of classes and events in VBA..

Thanks shg.. that indeed has opened new ways to solving problems..

**karan** · 04-29-2009, 06:04 PM

Hey shg,

That helped a lot!!! I am able to do it using Class Module.

Thanks a lot!!!

--Karan--

**shg** · 04-29-2009, 07:45 PM

Good job, karan, Wouyld you post your solution? This is not something that we do a lot of in the forum, and we'd all like to see what you did.

**karan** · 04-30-2009, 02:52 PM

Okay. Here is the solution that I figured out as a result of the guidance provided by Shg

. This is how it works:

In the add-in, you add a Class Module and name it 'CWorksheetObject'. In this class module, you write the following code:

Please Login or Register  to view this content.

In the add-in, you add a Module and write the following code in it:

Please Login or Register  to view this content.

Everytime you run the macro 'TestProc', the code which you wrote in the Class Module in the add-in works on any open workbook.

I am attaching an add-in to make the understanding easier for all those interested. Install the add-in by putting it in the relevant folder, followed by opening Excel and going to Tools >> Add-ins and selecting the add-in (its name is 'TextBox Popup Addin.xla'). It will create a Toolbar button in Tools (by the name 'TextBox Popup'). Now, everytime you open Excel and click on 'TextBox Popup', and click on any cell in the open Excel sheet that has some data, a textbox pops up with all the cell contents.

Any questions, do write back

Regards,
Karan
------------------------------------------------------------------------
Well, I just realized that we cannot attach an xla file (add-in)

. Moderator, is there a way I can do it? If I upload it on Rapidshare, it won't stay there for more than 30 days I guess. Please let me know what to do.

**karan** · 04-30-2009, 03:28 PM

Well, I just realized that we cannot attach an xla file (add-in) . Moderator, is there a way I can do it?

I am attaching a '.xls' file. Whoever wants to see how the above thing works, you can save the file as a '.xla' file with the same name, i.e. 'TextBox Popup Addin.xla' and follow the steps mentioned in the above post.

And somebody please check if it works in Excel 2007. I have tried it only in 2003.

--Karan--

**shg** · 04-30-2009, 07:03 PM

Thank you, karan.

BTW, you can zip and attach any kind of file.

**royUK** · 05-01-2009, 02:13 AM

Karan

Excel 2007 does not have Commandbars so it will not attach to he Tools Commandbar.

The addin can be found in the addins tab, the textbox is still creted

**royUK** · 05-01-2009, 02:57 AM

I have converted the addin to run with Excel 2007. A new tab is created - "TextBox Poup", there is one button in the tab to replace the one added to the Toolbar menu.

It should also run in earlier versions, adding the button to the Tools menu as the original did.