bring focus to workbook with variable name

[Jacob]

my program runs from a userform and uses one workbook. there is a link
on the userform, however, that can open another workbook. if this
other workbook is opened, the userform will interact with that workbook
since it is the active one. I need the original workbook to be
activated before manipulating the form again. I was able to solve this
using the following code in every section that referred to the original
workbook:

Dim WB1 As Workbook
Set WB1 = Workbooks("<filename>.xls")
WB1.Activate

I then realized that I have a save as button on the userform and the
workbook will often be saved with a different name rendering the above
code useless, or worse. is there a way around this? thanks.

 

[Guest]

The problem may be that you are using a constant for the filename. Once the
file has been save with a new name then:

Dim s As String
s = Selection.Worksheet.Parent.Name

should represent the latest filename. Then:

Set WB1 = Workbooks(s)

 

[Jacob]

thanks for your help. that should work, but now i am realizing that
there are a lot of other actions that need the original workbook to be
active. it seems silly to place this everywhere where there is
interaction. It seems that it would be best to some how activate the
workbook whenever that particular userform is being used. is there a
way to do this?

 

[Jacob]

I suppose a better question would be: is there a way to associate the
workbook with the userform and all its contents and actions regardless
of the file name?

 

[Guest]

I don't know.

If I need to refer to both an original workbook and the current workbook, I
create two Public variables for the references and initialize them with the
workbook open event. I don't know if this will save you any time, however.

 

[Jacob]

Thanks for your help. I'm not sure if that is what I need though. I
started a new thread after doing a little brainstorming. I still don't
know the best way to go about this.

http://groups-beta.google.com/group...ramming/browse_thread/thread/01353d9a9ca0faf5