Excel Format Freeze Panes with Access VBA

  • Thread starter Thread starter Guest
  • Start date Start date
xlSheet.Cells(intRow, intColumn).Select
xlApp.ActiveWindow.FreezePanes = False
xlApp.ActiveWindow.FreezePanes = True

BrerGoose
 
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.
 
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
 
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
 
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
 
Back
Top