• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Freeze at a row

M

mr_hiboy

Member
Hi,


Hopefully an easy one!


I'd like to be able to freeze at row 13 (my table headers), but having it stay att he top of the window, i don't want to see the info above as i scroll further down to save screen space?


Possible?


Thanks in advance
 
Scroll down so that row 13 is at the top of your screen. Select row 14, and then go to View - Freeze Panes.
 
Hi, mr_hiboy!

If Luke M's suggestion isn't what you're looking for, could you please express your request in other words? I didn't understand this: you want to freeze table headers (13 rows), but you don't want to see THAT info above as you scroll down. Thank you. Discard this message if yet solved.

Regards!
 
Hi, thanks for the replies and apologies for slow reply, away for the weekend.


Unfortunately Luke thats not what I am looking for. I want to be able to see all rows, but when i scroll down the info in rows 1:12 isn't important to see, so I want row 13 (table headers) to stay at the very top as i scroll, with rows 1:12 out of view.


Your suggestion means i can't see rows 1:12 as I scroll back up.


Cheers

Paul
 
Hi, mr_hiboy!


Place this code in the worksheet VBA section:

-----

[pre]
Code: 
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' constants
Const kiRowsTitle = 13
Const kiRowsPerScreen = 55
' declarations
Dim lRowFirst As Long, lRowsHidden As Long
' start
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
' process
'  define
Select Case Target.Row
Case Is <= kiRowsPerScreen
lRowsHidden = 0
Case Else
lRowsHidden = Target.Row - kiRowsPerScreen
If lRowsHidden >= kiRowsTitle Then lRowsHidden = kiRowsTitle - 1
End Select
'  hide/unhide
Range(Rows(1), Rows(kiRowsTitle)).Hidden = False
If lRowsHidden <> 0 Then Range(Rows(1), Rows(lRowsHidden)).Hidden = True
' end
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
[/pre]
-----


Set constant kiRowsPerScreen to screen visible rows (including titles) and give it a try.


Regards!
 
hi, nope not working. Rows 1:12 staying visible at all times.


Tried various kiRowsPerScreen to see I could get it to work.


Cheers
 
Hi, mr_hiboy!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Freeze%20at%20a%20row%20%28for%20mr_hiboy%20at%20chandoo.org%29.xlsm


Regards!
 
Yeah same issue, as I scroll I want rows 1-12 to disappear until the top row is 13, then keep 13 at the very top.


Rowa 1-13 are always on show in your example.


Thanks
 
Hi, mr_hiboy!

My uploaded file behaves correctly in my PC, you select A1 cell, move down with arrow down key, and when you reach cell A56, row 1 gets hidden, when at A57 row 2, and so on until only row 13 keeps displayed, which is reverted when you scroll up.

PC or Mac? Excel version?

Regards!
 
Hi, mr_hiboy!

Same as me, it should work. If my uploaded file doesn't work, check if you have macros enabled, automatic calculation and enabled events; if it does but your actual file doesn't, consider uploading that sample file.

Regards!
 
Just checked again on the work network and even your example doesn't work.


File saved as Macro enabled, calcs on, not sure what enabled events is? But other bit of vb and macros work in the file.


Cheers
 
Hi, mr_hiboy!


Clicked again in the DropBox link, got the file, opened, clicked on A1 cell, down with the arrows one cell at a time, and nothing happened until reaching row 56, then it began hiding rows from 1 thru 12, and stopped there. Then went upwards and everything back Ok. I don't understand.


Regards!


PS: EnableEvents is a property from Excel options. You'd check its value writing at immediate window:

Print Application.EnableEvents


@b(ut)ob(ut)hc

Hi!

If you're there checking my Caribbean islands list, would you be as kind as to download the file from below, repeat the described procedure and check if it works fine? Thank you very much.

Regards!
 
Here's my file for you to look at.


https://dl.dropboxusercontent.com/u/9071274/Scroll_Issue.xlsm


Appreciate your help (it's likely to be something I've missed! :)
 
Hi, mr_hiboy!

Don't care about your file, forget about it for a while. Focus on my uploaded file to DropBox, the main issue is that file doesn't work for you. And it should as it does in two different machines here with same OS and Excel versions.

Regards!


EDITED


PS: Tested your file, and when reaching row 44 it begins to hide from row 1 in advance.
 
Ah i see the issue.


If you scroll using the scroll bar or mouse wheel it doesn't work.


Like you say, if you use the down arrows it doesn't.


It would need to work with scroll to be most useful.


any thoughts?


Thanks
 
Hi, mr_hiboy!


No clue to any solution, Excel can only handle the following worksheet events:

Activate

BeforeDoubleClick

BeforeRightClick

Calculate

Change

Deactivate

FollowHyperlink

PivotXXX (many)

SelectionChange


There's no mouse scroll event. And neither any of the workbook events is suitable for this case.


So I'm afraid that you'd have to use the keyboard.


Regards!
 
I get the same result as SirJB7

Except that if I keep pressing the down arrow it starts re-scrolling at row 67

Windows 7, Excel 2010
 
Thanks for your help, might have to do for now.


I've read something about a moursewheel fix, seems excessive.


http://office.microsoft.com/en-gb/excel-help/add-support-for-the-scroll-wheel-to-the-microsoft-visual-basic-for-applications-6-environment-HA010117590.aspx
 
Mr_Hiboy

That MS articles only refers to how to make a scroll wheel work in the VB Editor,

not within VBA programs, which is what you require.
 
Hi, mr_hiboy!

Glad you to help you, even if you solved it partially. Thanks for your feedback and the software installed at that link seems to provide new events to be trapped, maybe that's your future solution, but take care that you'll have to install that in every machine that might use your workbook.

Welcome back whenever needed or wanted.

Regards!


@Hui

Hi!

Thank you.

Regards!
 
This might assist in tracking mouse scroll events:

http://www.cpearson.com/excel/DetectScroll.htm
 
Apologies for being a pain. But I can't seem to get it to work in my file.


Your example works fine and I've copied the exact code to to my file (see dropbox file above), but not working.


Any chance you could have a quick look?


Cheers
 
Also is there a reason why using this code would prevent "undo" - when I have the code in place if i type in a cell and then undo, it doesn't work. remove the code and it works fine.


Any pointers would be great on the above.


thanks
 
You must log in or register to reply here.
Back
Top