Refer to a named range in a seperate workbook

[sgltaylor]

Hi All,

I would appreciate some help with the following:

I have created an add in which displays a userform. Part of the
routine involves a look up to a named range called “currency” on a
worksheet called “rates”. The problem is that since the named range is
not in the active workbook the look up formula returns an error.

Any idea on how to refer to a named range contained within the add-in
and not in the active workbook.

Thanks,

Steve

 

[Patrick Molloy]

ThisWorkbook refers to the workbook hosting (ie running) the code. Sine the
range requested is from the addin, use

set thisrange = ThisWorkboook.Worksheets("Rates").Range("currency")
'where thisrange is defined as a range object
or

ccy = ThisWorkboook.Worksheets("Rates").Range("currency").Value
'where ccy could be string or double depending on the data in the cell

 

[sgltaylor]

Hi All,

I would appreciate some help with the following:

I have created an add in which displays a userform. Part of the
routine involves a look up to a named range called “currency” on a
worksheet called “rates”. The problem is that since the named range is
not in the active workbook the look up formula returns an error.

Any idea on how to refer to a named range contained within the add-in
and not in the active workbook.

Thanks,

Steve

Hi Patrick,

Thanks for the suggestion. However, I keep getting an object required
error.
Any idea on what I am doing wrong?

Thanks,

Steve