MS-access VBA code does not add Excel's top row freeze correctly

ino_mart

Registered User.
Local time
Today, 02:39
Joined
Oct 7, 2009
Messages
78
All

I have an Access application which creates an Excel-file with multiple sheets by using VBA. For each Excel-sheet I want a "freeze on the top row".

I tried several things with sample code I found on the Internet, but the freeze is always placed between column G:H and row 4:5. This results in
  • rows 1:4 are always visible during scrolling downwards
  • columns A:G are always visible whislt scrolling to the right.
I currently have subroutine below. oxl is the Excel.application-object. wrk is the oxl.workbook.

As soon the Excel-object is created (in some previous step), it is set to invisible by using oxl.visible=false. The issue also occurs when the property is set to true.

Does someone have a solution?

Private Sub FreezePane(ByRef oxl As Object, ByRef wrk As Object)
Dim x As Worksheet
For Each x In wrk.Worksheets
wrk.Worksheets(x.Name).Activate
oxl.Range("A1").Activate
oxl.Range("A1").Select
oxl.ActiveWindow.FreezePanes = False
oxl.ActiveWindow.ScrollRow = 1
oxl.ActiveWindow.ScrollColumn = 1
oxl.ActiveWindow.FreezePanes = True
oxl.ActiveWindow.ScrollRow = oxl.ActiveCell.Row
Next
End Sub
 
For starters have you stepped through the code to see what is actually happening instead of pasting code and scratching your head when it does not work?
 
I did debug the code, but the problem is that this "freezing pane"-code does not select anything. So you don't see what happens, even if the Excel-object is visible.

So I went searching on the Internet and quickly found out other programmers also experienced problems with the "freezing pane"-instructions via Access VBA-code. So that's why I started mixing the code with what others tried. But none of what I found works as expected.
 
Try the below code:
Code:
Private Sub FreezePane(ByRef oxl As Object, ByRef wrk As Object)
  Dim x As Worksheet
  For Each x In wrk.Worksheets
    wrk.Worksheets(x.Name).Activate
    oxl.Range("A2").Select
    oxl.ActiveWindow.FreezePanes = True
  Next
End Sub
 

Users who are viewing this thread

Back
Top Bottom