Hide or Freeze

[Barry]

I have a worksheet that I'd like to modify to be able to scroll a certin
number of lines before changing where the freeze is located.
I'm using Excel 2003
ie..

My sheet is less than 200 rows long.
The top 5 rows are frozen.
There is a header so to speak on row 47 that when I scroll down to it I
would then like to freeze from there up.
On scrolling back upwards I'd like to change the freeze back to line 5.
I've looked around and can find no reference to this, but it seems so
simple.

I'd like to thank all who respond before hand!

Barry

 

[JLatham]

One problem is that when you get down to row 47 and freeze the window there,
you can't just simply scroll back up to row 5.

Here's one possible solution. Start by opening the workbook and pressing
[Alt]+[F11] to get into the VB Editor. In it, choose Insert --> Module.
Then copy the code below and paste it into the empty module presented to you.
Close the VB Editor.

Go to the sheet where you want to do this and start by making row 1 (or 2,
3, 4 or 5) about 2 or 3 times the normal height. We're going to stick a
"button" in there.

Use View --> Toolbars and select the Drawing toolbar. We could use the
Forms toolbar, but I like the Text Box tool in the drawing toolbar for things
like this. Select the Text Box and draw a text box in the row you made
taller. Make the text something like [Jump to Section 2]. Right-click on
the shape's edge and choose Attach Macro, and choose the JumpToRow47 macro.

Now scroll down to row 47 and make it taller and put another text box in it,
with text something like [Jump to Section 1]. Again, right click on it and
choose Attach Macro, this time choosing the JumpToRow5 macro.

Try them out by simply clicking on them. If nothing happens, check your
Macro security level, it may be too high, should be set on Medium so you get
an option to disable macros in workbooks that you don't trust. You have to
close Excel after changing the Macro Security Level setting. The new setting
takes effect the next time you open Excel.

If things work right, press [Alt]+[F11] again and change the defining line
of the macros from "Sub ..." to "Private Sub..." This will keep them from
appearing in the Tools --> Macro --> Macros list which will prevent someone
from trying to use them from there while on the wrong worksheet.

Here's the code:

Sub JumpToRow5()
ActiveWindow.FreezePanes = False
Application.Goto Range("A1"), True
Range("A6").Select
ActiveWindow.FreezePanes = True
End Sub
Sub JumpToRow47()
ActiveWindow.FreezePanes = False
Application.Goto Range("A47"), True
Range("A48").Select
ActiveWindow.FreezePanes = True
End Sub

I hope this helps you at least a little.

 

[Barry]

JLatham,

Thank you for your response. I tried it and it works for what I need.
I had something much more grander in mind but just have no idea how to write
the code.
Your explaination about my somewhat limited request was on spot.

 

[JLatham]

You sound disappointed? What "much more grander" thing did you have in mind?

You could do something similar without the buttons by, for example, setting
up to jump to the other section by double-clicking in one of the cells in
rows 1-5 and/or 47, but that's not very intuitive for the end user.

The code below would accomplish that (double-click in any cell in rows 1-5
and it goes to freeze at 47, double-click in any cell in row 47 and it goes
to freeze at 5).

Code goes into the worksheet code module: right-click on the sheet's name
tab and choose [View Code] and copy and paste the code into it and enjoy.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
'check if in 1st 5 rows
'or on row 47, if not, quit
If Target.Row > 5 And Target.Row <> 47 Then
Exit Sub
End If
ActiveWindow.FreezePanes = False
Select Case Target.Row
Case Is = 47
Application.Goto Range("A1"), True
Range("A6").Select
Case Else
'must be 1 thru 5
Application.Goto Range("A47"), True
Range("A48").Select
End Select
ActiveWindow.FreezePanes = True
End Sub