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
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?
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.
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