Problems freezing the header row form scrolling

[kiekar]

Hello,

I've created a userForm to connect to an access database to copy data to a Worksheet. This works fine when the data is copied to "Sheet1".
When the data is copied to "Sheet2" an error is generated. "Select Method of Range class failed.
[VBA]
objWorksheet.Rows(2).Select
objExcel.ActiveWindow.FreezePane = True
[/VBA]

Even when the error is generated, the data is still copied to "Sheet2" but the header row scrolls.

I've attached a doc with the copy data routine only. The error is generated at line 71

Any help would be much appreciated.

Thanks

Karl

[XLGibbs]

Make sure the sheet 2 is the active window. If sheet 1 already has frozen pane, it might generate that error.

Haven't check the file yet, but the first thing to check would be if Sheet2 is in fact the active window.

[kiekar]

Hello,

Thanks for the reply.

How would I make Sheet2 the active window?

Regards

Karl

[XLGibbs]

If the name of Sheet2 is "Sheet2" it would be:

[VBA]
Set objWorksheet = Sheets("Sheet2")
[/VBA]

[kiekar]

Hello,

Once again thank you for your response. Unfortunately I'm still getting the error.

Thanks

Karl

[Charlize]

[VBA]Worksheets(2).Activate
Worksheets(2).Rows(2).Select
ActiveWindow.FreezePanes = True[/VBA]
Are you doing this in excel ? Because you are creating an instance of excel ... in excel ???

Charlize

[kiekar]

Hello Charlise

Thanks for the tip I was able to get it to work with objWorksheet.Activate

Regards

Karl