Keep UserForm Linked to Particular Workbook Even if Focus is Lost

**prattleon** · 01-19-2010, 12:03 PM

I have an excel program that uses a modeless userform. often times i will open other spreadsheets while the form is active or minimized. if i return to the form and run it without manually bringing focus to the appropriate workbook, i get an error because it references the wrong workbook.

is there a way to keep that form tied to that workbook no matter what? also, it is important that this procedure works despite the workbook name changing.

thanks!

**royUK** · 01-19-2010, 12:06 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**prattleon** · 01-20-2010, 11:46 AM

essentially I just want to know how to keep a userform focused on a workbook that might have a changing name. if there are multiple workbooks open, the userform must interact only with the workbook it was intended. if the name never changed it would be as simple as setting workbooks("filename.xls").active for each action on the userform, but "filename.xls" may change to anything. so i need to get the workbook name when the userform initializes:

Dim wkb As Workbook
Set wkb = ActiveWorkbook

and then use:

workbooks(wkb).active

but the first part has to be done only in the initialization sub (so that it sets wkb to the correct workbook -- the one that initializes the form) and the other part in the other subs. but i am having trouble sending the variable wkb from one sub to another. is there an easier approach? more efficient?

**romperstomper** · 01-20-2010, 12:21 PM

If the userform is in that workbook, then refer to ThisWorkbook, not to ActiveWorkbook. You can also use the Workbook's Activate and Deactivate events to show and hide the form as required.

**prattleon** · 01-20-2010, 01:48 PM

Thank you. I used:

With ThisWorkbook
.Sheets("Sheet1").Range("A1:J10").Copy Destination:= _
.Sheets("Sheet2").Range("B30")
End With

i did this everywhere and it appears to work. I thought ThisWorkbook referred to the workbook that had focus at the time that code was executed.

Thanks again!