Excel Format Freeze Panes with Access VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey,

Can someone fix this syntax?

objXLSheet1.Range("2:2").FreezePanes = True
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

formating spreadsheet 3
open Excel from Web: freeze panes so 1st row is always displayed at top? 1
Freeze Panes loss 3
Excel VBA 1
Freeze panes 4
Access VBA for Page Setup in Excel 0
Excel Freeze panes in Excel 3
Excel VBA - counting cells by colors and names to it in two different rows. 0

Back
Top