PDA

View Full Version : Freeze panes shifting on worksheet



s.schwantes
01-09-2015, 07:09 PM
I have multiple Form Control buttons used to navigate to other locations on a ws which would otherwise require a lot of scrolling and/or paging up/down.

At the top section I have several buttons labeled Go To and then in the target areas I have buttons labeled Go Home to return to range "A1".

Here's the code from one of the buttons:



Sub GoTo_IncomeStmtConsol()
Application.ScreenUpdating = True
Application.Goto Reference:="Income_Stmt_Consol"
ActiveWindow.SmallScroll Down:=30
ActiveWindow.FreezePanes = True
End Sub

What I don't understand is sometimes this works just fine. The macro runs, goes to the designated named range, which in this case is "Income_Stmt_Consol" which also happens to be cell C91 ... and the panes freeze around the range with both vertical and horizontal panes as you'd expect w/ C91 in the top left corner. However, at the moment it's just locking panes horizontally at row 109.

Puzzling.

Any one have an explanation or solution to this problem - which does seem to be intermittent.

Thanks!!

Steve

Paul_Hossler
01-09-2015, 07:30 PM
See if this helps. I was not sure why you have the .SmallScroll




Sub GoTo_IncomeStmtConsol()
Application.ScreenUpdating = True
ActiveWindow.FreezePanes = False
Application.Goto Reference:="Income_Stmt_Consol", scroll:=True
ActiveWindow.FreezePanes = True
End Sub

s.schwantes
01-10-2015, 10:09 AM
Paul,

Thanks for responding to the post. I tried the code you suggested. It helps but it's not the solution. Now instead of freezing the panes solely horizontally at row 109 it's now locking both Hly and Vtly at cell E103. Which appears to be dead center of the visible screen creating four equal quadrants; rather than making C93 the top left corner.

Regards,

Steve

SamT
01-10-2015, 12:17 PM
Sub GoTo_IncomeStmtConsol()
Application.ScreenUpdating = True
ActiveWindow.FreezePanes = False

Application.Goto Reference:=Range("Income_Stmt_Consol"), scroll:=True
Range("Income_Stmt_Consol").Cells(1, 1).Activate 'Adjust Cells as desired

ActiveWindow.FreezePanes = True
End Sub

s.schwantes
01-10-2015, 01:45 PM
Sub GoTo_IncomeStmtConsol()
Application.ScreenUpdating = True
ActiveWindow.FreezePanes = False

Application.Goto Reference:=Range("Income_Stmt_Consol"), scroll:=True
Range("Income_Stmt_Consol").Cells(1, 1).Activate 'Adjust Cells as desired

ActiveWindow.FreezePanes = True
End Sub

Sam - thanks a million! I think this is going to work ... modified my code as such:


Sub GoTo_IncomeStmtConsol()
'
Application.ScreenUpdating = True
ActiveWindow.FreezePanes = False
Application.Goto Reference:=("Income_Stmt_Consol"), Scroll:=True
Range("Income_Stmt_Consol").Cells(2, 2).Activate
ActiveWindow.FreezePanes = True
End Sub
Sub GoTo_RebateTablesConsol()
'
Application.ScreenUpdating = True
ActiveWindow.FreezePanes = False
Application.Goto Reference:=("RebateTablesConsol"), Scroll:=True
ActiveWindow.ScrollColumn = 22
Range("RebateTablesConsol").Cells(2, 1).Activate
ActiveWindow.FreezePanes = True

had to adjust the placement of my named range in the first sub - but this now works ...

Regards,

Steve