Excel Format Freeze Panes with Access VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DCPan - 21 Sep 2007 01:20 GMT

Hey,

Can someone fix this syntax?

objXLSheet1.Range("2:2").FreezePanes = True

Reply to this Message

BrerGoose - 21 Sep 2007 05:10 GMT

xlSheet.Cells(intRow, intColumn).Select
xlApp.ActiveWindow.FreezePanes = False
xlApp.ActiveWindow.FreezePanes = True

BrerGoose

Reply to this Message

DCPan - 21 Sep 2007 18:00 GMT

OK, so why can't I freeze pane multiple tabs?

'Format various worksheets in the workbook
objXLSheet1.Range("A1:S1").Font.Bold = True
objXLSheet1.Range("A:S").Columns.AutoFit
objXLSheet1.Range("A1:S1").HorizontalAlignment = xlCenter
objXLSheet1.Range("2:2").Select
objXLApp.ActiveWindow.FreezePanes = True

objXLSheet2.Range("A1:S1").Font.Bold = True
objXLSheet2.Range("A:S").Columns.AutoFit
objXLSheet2.Range("A1:S1").HorizontalAlignment = xlCenter
objXLSheet2.Range("2:2").Select
objXLApp.ActiveWindow.FreezePanes = True

This code works if I don't free pane the 2nd tab.

> xlSheet.Cells(intRow, intColumn).Select
> xlApp.ActiveWindow.FreezePanes = False
> xlApp.ActiveWindow.FreezePanes = True
>
> BrerGoose

Reply to this Message

BrerGoose - 21 Sep 2007 21:22 GMT

You have to switch to each sheet, and then set the property for FreezePanes.

Set xlSheet = xlApp.ActiveWorkbook.Sheets(1)
xlSheet.Activate
xlSheet.Cells(intRow, intColumn).Select
xlApp.ActiveWindow.FreezePanes = False
xlApp.ActiveWindow.FreezePanes = True

Set xlSheet = xlApp.ActiveWorkbook.Sheets(2)
xlSheet.Activate
xlSheet.Cells(intRow, intColumn).Select
xlApp.ActiveWindow.FreezePanes = False
xlApp.ActiveWindow.FreezePanes = True

BrerGoose

Reply to this Message

DCPan - 21 Sep 2007 21:36 GMT

Thanks! U ROCK!

> You have to switch to each sheet, and then set the property for FreezePanes.
>
[quoted text clipped - 11 lines]
>
> BrerGoose

Reply to this Message

Rod - 28 Sep 2007 05:27 GMT

I wonder if you might be able to help me. I have the following code segment:

ActiveWindow.Activate
With ActiveWindow
' Turn off FreezePanes first to remove Split
.FreezePanes = False
.Split = False

' Workaorund a problem to make sure Freeze occurs in the
correct cell!!
Range("A1").Select

Range(x).Select
.FreezePanes = vbTrue

End With

x contains something like "J:4". The problem mentioned is that for some
unknown reason, the freeze occurs at N760 or some such. The "760" is within
a coupe of rows or so of my spreadsheet.

If I trace the code, it seems to work.sometimes. I've traced it through the
Range(x).Select, with x="J4", and then the .FreezePanes goes screwy (a very
technical term!).

Your generous assistance would be greatly appreciated!
Rod

Reply to this Message

BrerGoose - 28 Sep 2007 16:21 GMT

Specify the cell not a range, and turn the freeze off then on, as follows....

xlSheet.Cells(intRow, intColumn).Select
xlApp.ActiveWindow.FreezePanes = False
xlApp.ActiveWindow.FreezePanes = True

BrerGoose

Reply to this Message