Code TO HIDE or UNHIDE Row.

[Mister H]

Hi:

I am using the code below on my spreadsheet. What is does is hides Row #1 until Row 41 is reached. When 41 is reached Row 1 appears. Unfortunately (for me) I need to alter this code and was wondering if anyone could tell me if it is possible. First the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Rows(1).Hidden <> (Target.Row < 41) Then
    ActiveSheet.Unprotect
    Application.ScreenUpdating = False
    Rows(1).Hidden = Target.Row < 41
    ActiveSheet.Protect
    Application.ScreenUpdating = True
    End If

End Sub

What I would now like to happen is that Row 1 stays hidden UNTIL Row 16 is OFF Screen. Can that be done? What has happened is that my row 16 contains the heads for my input table. I have now had to set the rows to resize to accommodate data input. If no rows are resized then Row 16 stays visible until I reach row 40. When row 41 is reached then Row 1 appears which contains my headers as well. I hope this makes sense to someone...

SO, IF I can get a code that would keep row one hidden until Row 16 is off screen then it will not matter what rows expand.

Any ideas anyone???

THANKS,
Mark

 

[RalphA]

I don't understand too well what you are after, but, it occurs to me that, maybe, you want the headers to remain visible all the time. If so, then I would just click on row 2, then on Window, Freeze Panes. Is this what you need?

 

[Mister H]

H RalphA:

Sorry for the delay in replying but I just saw your post...

You are correct in that I want the Headers to be visible at ALL times but unfirtunately the Header row is Row 16 not Row 2. If I Freeze Row 16 it only leaves about 2 rows visible for entering data due to the Information that is contained in Rows 2 to 15.

If there is a way to make the Window Freeze one it reaches Row 16 that would work as well.

Any ideas?

THANKS,
Mark

 

[RalphA]

But, if "row 1 contains the headers as well", why don't you just freeze only pane 1, thereby showing the headers at all time?

 

[TrippyTom]

Hmm.... this sounds like you could adjust your layout a little better. What's in all those rows that you want to have on every page? Could you put it in the actual HEADER instead? (in FILE > PAGE SETUP)

If so, you could delete all those rows from the worksheet (putting it in the header instead) and set your "rows to repeat at Top" to only row 1 and free up a lot of screen real estate.

 

[Mister H]

Hi:

Confusing hey...

The reason I also have the Headers in Row 1 is due to the fact that I could not Freeze Row 16 (ACTUAL Header Row) once it reached the top and I could not Freeze Rows 1 to 15 because as I mentioned earlier this only leaves a couple of data rows visible which seemed to confuse the Users.

So the Solution (requested by me and supplied by the Board) was to put the headers in Row 1 and hide it. Once Row 41 was reached Row 16 would be off screen and then Row 1 would appear so that the Users ALWAYS had a Header Row. I hope this makes sense

It is not the best design but it did work until I found out that the Rows had to be set up to Autofit to accomodate the data that some users had to enter. Now of course hen 1 or more rows change height this effects when Row 1 should appear.

THANKS,
Mark

 

[Mister H]

Hi TrippyTom:

I know the layout from an Excel point of view is not good but the users wanted a pretty printout.

As for what goes in rows 2 to 15 are things like Client Name (Drop Down List), Account Number, Persons Name that is entering Data, Totals on what is contained in Rows 17 to 266, Deposit Slip Number, Amount and Date. These are not things that are entered in the Actual Data fields. Have I confused you a little more?

Hope this clarifies things a little. If not let me know and I will babble some more...

THANKS again to everyone who is trying to come up with a solution.

Bye 4 Now,
Mark

 

[Mister H]

Help!!! PLEASE.

Hi All:

Does anyone on the morning crew have any ideas or solutions for me???

THANKS,
Mark

 

[TrippyTom]

Part of the problem is everyone uses different resolutions. For instance, I see way more than 41 rows on my screen because I run in 1600x1200 mode. As far as I know, there's no way to know if a row has gone "off-screen" because screen dimensions vary so much.

Your best bet is to modify your layout. Is there any reason all those header rows couldn't go onto a separate worksheet?

 

[Mister H]

THANKS Tom,,,

I know the layout is not good but unfotunately I will need to stick with it for now as I have too much time invested.

The sheet I am referring to with the headers feeds about 25 different hidden sheets and I know if I start modifying at this late stage things will get messy.

For now I will just use the code I have and the Users will just have to deal with it.

Take Care,
Mark