Excel Runtime error via Internet Explorer

[wadjssd]

I have built an Excel model which performs various convoluted calculations based on dropdown boxes and text box input.
One element takes the product of values from 2 text boxes and divides this by an exchange rate, which is selected via a dropdown box. The various exchange rates are held in the background on the spreadsheet itself, in the cells immediately below a named-range cell called "ExRate".
When run as a standalone in Excel, this all works fine. However, a colleague has been trying to run the model over an intranet through Internet Explorer. Everything works fine except for this one reference to a named range. It keeps throwing up the dreaded

"1004 method "Range" of object "_Global" failed"

Here's the line of code that the debug jumps to:

Txt_OrderFOB£.Value = FormatCurrency((Txt_FOB.Value * Txt_BuyQty.Value) / Range("ExRate").Offset(Cbox_Currency.ListIndex, 0).Value, 2)

I'm not sure why this is happening specifically through IE. Is some part of Excel's ability to refer to itself lost when run this way?

Any help greatly appreciated.

Regards
wadjssd

 

[MJPPaba]

It sounds like the cursor focus is in the text box which I believe is held outside of the scope of the workbook object. Try to put a line of code that takes the focus away from the textbox and to the sheets object.

Just a thought.

Regards

Matt

crazy times call for crazy people


CrazyPabs

Is it lunchtime yet?

 

[wadjssd]

Hmmm, not sure about this.
When the form is initialised, the code uses "Range" references to populate the dropdown lists...so I can't understand why it seems to be losing the link back to the spreadsheet once the form is loaded...plus why this is only happening when run via IE.
I've tried pointing the code specifically back at Excel by changing

Range("ExRate") to

Excel.Range("ExRate")

but to no avail.

Most frustrating (

wadjssd

 

[muellems]

Hi,

perhaps the binding of the "range" function is the problem. Try the use the "range" function fully qualified, e.g.

  mySheetsName.Range("Foo")

For debugging purposes, you could also list all names of named ranges for getting a clue what's going on. Therefore you have to use the "Names" collection, also with the right qualifier.

HTH,
Markus

--
VBA is about marketing, not about good programming