Referencing a named range without specifying the worksheet

[cardiac]

I have an Excel VBA app that works fine when I open it as an Excel file, but gives a "Run-time error 1004: Method 'Range' of object '_Global' failed" error when I hyperlink to it using Internet Explorer 5.5 (it fails when it hits the following line

[tt]Call populateExpertise(cbo, Range(strNissanOrg & "_General_Expertise"))[/tt]

The procedure 'populateExpertise' takes a ComboBox and a dynamic Range as arguments and then loops through the range, adding each value to the Combobox.

I had a workaraound to this problem by adding the worksheet reference to the Range reference e.g.
[tt]Call populateExpertise(cbo, sheet3.Range("NTCE_General_Expertise") [when strNissanOrg = "NTCE"][/tt]

The problem is that now, the Ranges are on different sheets.

Why does the app work when called using Excel, but fail when called using a hyperlink in Internet Explorer 5.5?

Can anyone help?

 

[PHV]

You may try this:
Call populateExpertise(cbo, Names(strNissanOrg & "_General_Expertise").RefersToRange)
or this:
Call populateExpertise(cbo, ActiveWorkbook.Names(strNissanOrg & "_General_Expertise").RefersToRange)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cardiac]

Thanks for the reply, PH.

Unfortunately, the proposed solution doesn't fix the concern. The error message changes to "Method 'Names' of object '_Global' failed.
(Again, it works fine when I open it as an Excel file, but not when I hyperlink to it through IE5.5)

Any further help you can offer will be gratefull received!

David