Using Refedit with other workbook

[Steve]

I need to let the user select ranges from other open workbooks. The Refedit
doesn't seem to allow switiching to another workbook when it is in use. Does
anyone know any way around this for excel 2003?

 

[NickHK]

Steve,
Seems that the RefEdit can only work with the activeWorkbook, so Activate
the required book:

Private Sub UserForm_Initialize()
Workbooks("Book2.xls").Activate
End Sub

NickHK

 

[Steve]

Is there any way to do this without knowing the name of the workbook or
worksheet (the user chooses the workbook, in which I don't necessarily know
the name of the workbook)?

 

[Dave Peterson]

If there's something unique in that other workbook, you could look for it and
then activate the workbook where you found it.

If you opened the other workbook in code, then you know what workbook to
activate:

In a General module

Public OtherWkbk as workbook

....later in someroutine...

set otherwkbk = workbooks.open(filename:=....)

Then in your useform code:
Private Sub UserForm_Initialize()
otherwkbk.activate
end sub

Or you could tell the user to choose from the list of open workbooks/windows
under the Window option on the worksheet menubar.

 

[Steve]

How would you do this in code?

"Or you could tell the user to choose from the list of open workbooks/windows
under the Window option on the worksheet menubar."

 

[Dave Peterson]

I think telling the user to use that window option is more of a training issue.
But there's nothing wrong about adding a label to the userform with instructions
to the user.

Or maybe add a help button to the userform.

If you don't want to become a .hlp/.chm author, you may want to look at how John
Walkenbach does it:

http://j-walk.com/ss/excel/tips/tip51.htm