Installing events into all sheets automatically?

Greetings to all professional excel-users and -programmers! Very useful forum, really!

I just made my first steps with Visual-Basic in Excel beacause my boss wants me to create something like a converter.

So here's the point:

In one sheet, there will be thousands of values listed upside-down while every coluns specifies one field (just as it would look in access). The problem is, that the same lists in our company have the columns sorted in a different order. So the user had to find out, which column corresponds to which and had had to copy and paste these columns manually for to get to our format.

Now my idea is to guide the user throug all of our columns and let him select the corresponding column in his file. This is relatively simple to implement using controls like listbox (I already have some experience in that stuff). But my big problem is the following:

For to get the event of selection change, I have to use "Worksheet_SelectionChange" which has to be "installed" inside the worksheet I need to convert. But the user shouldn't have to do this by himself, beacause his files shouldn't be changed at all.

The user should just import my module and run it. So now, let's ask the question:

Is there another Event-Procedure than "Worksheet_SelectionChange" for to get the selection-change-event independently of the sheet to be converted?

or

Is there a possibility to create this modules automatically by my module by adding them temporarly?

The procedure to insert would be just these three lines:

Private Sub Worksheet_SelectionChange(ByVal Target As range)
Run "MyModule_TheMacroDoingallthestuffIneed", Target
End Sub

Maybe there is a solution, maybe not (actually I don't think so), but perhaps anyone could help me or just give me hints?

Thanks in advance!

Quote

Originally posted by Egad
How about something like this in your ThisWorkbook module?

Thanx, but this doesn't really solves my Problem. Our Clients work with their own Workbooks and they shouldn't be chanched too. In fact, as I tried this little Program, the example-file I had didnt even wanted to include this macro beacause of the security-level which was set to High.

So what I really need is a solution that can be programmed in one single Module-file (bas). The user shall just import this single file and run the macro.

But anyway thanks for your help. I did'n knew this function "Workbook_SheetSelectionChange". Seems to be quite useful.

Quote

Originally posted by royUK
Have you considered creating an addin?

Never heard of that but sounds good.

---

I started to search doc and examples for that. So now after two hours: Is that a joke?:o

No really, they can't be serious! First I had to configure my Excel for Add-ins (Creating menuitems). Then I started to examine the examples I found at forums and professional pages. The first examples I found didn't work at all: Excel turned off and the screen began to fill itself with thousands of windows -> restart. After the next, Excel wasn't responding anymore -> restart. For another addin I had to turn the security-level down to the neepest level and wholla! my keyboar-driver was set to us-en (I got a swiss-keyboard) -> setup and restart. the next I found was finally a simple, little and I think pretty understandable project of 5 KB for to count the number of characters written. But this thing needs in fact 1.5 MB disk space, would insert some kind of data into the registry (I always read the readme-file) and at last did't even worked too, Excel didn't recovered the exe as an add-in -> well, no restart but veeery impressive.

Thanks for your hint, but no, I will not use that! (don't feel depressed, it's nothing personal :))

EDIT:

Well anyway, my chef said that we will do this in text-format now. So thanks for all your hints. Maybe another time, I'll have a look back to this forum. Greetings!