Grab window handle with any click on a userform

[bmlkkidd]

Good morning all,

I'm using the typical method of using a window handle to re-activate an instance that launched a userform. However, I need to run the FindWindow routine from every control in order for it to work. I have well over 200 controls in the multi-page userform, and it will be extremely tedious to do this.

How else can I accomplish this? Here's the full scenario:

We have a quoting tool that launches a user form. The sales team will frequently call up or already have up other Excel workbooks. When they go back to the user form, if they did not click into the calling instance first, then the controls and control sources are looking at what Excel believes is the Active Workbook. So I need any click on the userform (whether a radio button, text field, combobox, etc.) to grab the quoting tool instance. I've used the window handle before as well as Workbooks("Quoting Tool").Activate in cases before, but I had to include a pointer to a module in each control's events.

What is the easiest way to accomplish this? Do I need a class module to monitor each type of control? Simply adding the the userform Activate or MouseMove/MouseDown events don't work for the individual controls.

 

[Dan_W]

Why don't you just use ThisWorkbook in the relevant userform code rather than ActiveWorkbook?

 

[bmlkkidd]

I can use either method, but what I am after is a way to run the code to set the active workbook regardless of where they click in the Userform, whether it be a textbox, combobox, radio button, the form itself, etc.