PRB: Inconsistent Results When Calling FreezePanes in OnLoad Event of Window (256625)


The information in this article applies to:

  • Microsoft Office Spreadsheet Component 9.0 1.0
  • Microsoft Visual Basic, Scripting Edition 5.0
  • Microsoft Internet Explorer (Programming) 4.01
  • Microsoft Internet Explorer (Programming) 5
  • Microsoft Internet Explorer (Programming) 5.01
This article was previously published under Q256625

SYMPTOMS

When you call the FreezePanes method of a Spreadsheet component on a Web page, the frozen pane may not appear on the spreadsheet as expected.

CAUSE

The FreezePanes method is dependent on the window. When a Web page is parsed, and occasionally during the window's onLoad event, the Spreadsheet component may not yet have a window because Internet Explorer sometimes creates the window after onLoad has fired.

RESOLUTION

To work around this problem, you can use the setTimeout method of the window in the onLoad event to call a routine that freezes the panes once onLoad completes. This solution is illustrated in the "More Information" section of this article.

MORE INFORMATION

The following HTML demonstrates the problem. The FreezePanes method is called in the onLoad event of the window of the page so that the panes freeze at cell A2. The FreezePanes method does not generate a run-time error but the frozen pane may not appear when the page loads. 
<HTML>

<BODY>
<OBJECT classid=clsid:0002E510-0000-0000-C000-000000000046 id=MySheet></OBJECT>
</BODY>

<SCRIPT Language=VBScript>

Dim c

Function Window_onLoad()
   Set c = MySheet.Constants
   MySheet.Cells.Clear
   MySheet.Range("A1").Value = "Number"
   MySheet.Range("A2:A50").Value = "=RAND()"
   MySheet.Range("A2").FreezePanes c.ssFreezeTop
End Function

</SCRIPT>

</HTML>
To work around this problem, create a separate routine that calls FreezePanes, and then call this routine by using the setTimeout method in the Window_onLoad event. The modified HTML with this workaround in place resembles the following when your page uses VBScript: 
<HTML>

<BODY>
<OBJECT classid=clsid:0002E510-0000-0000-C000-000000000046 id=MySheet></OBJECT>
</BODY>

<SCRIPT Language=VBScript>

Dim c

Function Window_onLoad()
   Set c = MySheet.Constants
   MySheet.Cells.Clear
   MySheet.Range("A1").Value = "Number"
   MySheet.Range("A2:A50").Value = "=RAND()"
   idTmr = window.setTimeout("Freeze",1,"vbscript")
End Function

Sub Freeze()
    MySheet.Range("a2").FreezePanes c.ssFreezeTop
End Sub 

</SCRIPT>

</HTML>

Note

If you are using JScript instead of VBScript, you can use the same workaround for setting frozen panes by calling a routine that uses the setTimeout method of the window.

REFERENCES

For more information on the Office Web Components, please visit the following support site:

http://support.microsoft.com/ofd

For additional information on using JScript to handle events, such as the window onLoad event, click the article number below to view the article in the Microsoft Knowledge Base:

183617 PRB: VBScript Style Event Trapping Does Not Work in JScript

For additional information about sample code that demonstrates solutions using the Office 2000 Web Components with VBScript and ASP, please click the article number below to view the article in the Microsoft Knowledge Base:

258187 OWebComp.exe Contains Scripting Samples for the Office Web Components

Modification Type:MinorLast Reviewed:8/23/2005
Keywords:kbOfficeWebSpread kbprb KB256625
©2004 Microsoft Corporation. All rights reserved.