Freezing/Splitiing Cells

[andrewb90]

Hello All,

I have been searching for answers to this and have not found anything helpful, so I am not sure if what I want is doable, but I'll see if anybody has some good Ideas. In the range A2:D18 I have some information I would like on the screen at all times. The user part of the worksheet is in columns E:L and it goes down to row 1000. Now I would like to be able to scroll down through the rows, while still keeping that data visible, but I do not want the first 18 rows frozen above. This takes up too much of the screen.

Thanks,

Andrew

 

[Peter_SSs]

This sounds like you would have to select what cells you need each time you select a new sheet. Additionally, the user would have to setup the watch window right? Sadly the person using my sheet will not be able/willing to do that.

1. Are you trying to watch cells in one particular workbook, or a number of different workbooks?


Assuming within one workbook ..

2. Are you trying to watch A2:D18 in one particular sheet, even if the user moves to another sheet, or

3. Are you trying to watch A2:D18 on just whatever worksheet is active?

4. Will a suggestion that involves macros be acceptable?

 

[andrewb90]

Hi Peter,

I am trying to watch cells A2:D18 on the active worksheet, well, 1 of 7 of the sheets. So not all of them. Each sheet represents a different day (labeled MON,TUE,WED...) In those sheets I have different macros running already, but would not be opposed to more.

To give you an idea of what each sheet looks like:
A2:D18 is a summary of 5 weeks of sales and averages for whatever day of the week the page is.
rows 20:219 are week 1 sales.
rows 220:419 are week 2 sales.
rows 420:619 are week 3 sales.
rows 620:819 are week 4 sales.
and rows 820:1019 are week 5 sales.
I have some macros in place to show and hide different sections of each week, as well as hide any and all weeks that are not in use. I also have Selection change VBA running on the page as well.
What I currently have in place in columns A:D frozen and G:N is where the main report info is. I have considered moving the sales averages to a top freeze but, it seems to cut off too much of the page, so I found freezing columns was far more effective for viewing.

Thank you,

Andrew

 

[Peter_SSs]

I am trying to watch cells A2:D18 on the active worksheet, well, 1 of 7 of the sheets. So not all of them.[/code]I'm afraid that's still not clear to me. I'll assume that it means you have sheets names MON, TUE, ..., SUN and whatever one of those sheets is active, you want to watch A2:D18 on that active sheet. If that is the case try this in a copy of your workbook.

1. Put these procedures in a standard Module

Sub SetUpWatchWindow()
  Dim c As Range
  
  Const WatchRng As String = "A2:D18"
  With Application
    .Watches.Delete
    For Each c In ActiveSheet.Range(WatchRng)
      .Watches.Add c
    Next c
  End With
  Application.CommandBars("Watch Window").Visible = True
End Sub

Sub CloseDownWatchWindow()
  With Application
    .Watches.Delete
    Application.CommandBars("Watch Window").Visible = False
  End With
End Sub

2. Put these procedures in the ThisWorkbook Module

Private Sub Workbook_Open()
  SetUpWatchWindow
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  CloseDownWatchWindow
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
  SetUpWatchWindow
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
  CloseDownWatchWindow
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  SetUpWatchWindow
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
  CloseDownWatchWindow
End Sub

3. When it opens, resize/position the Watch Window as it suits you the best.

 

[andrewb90]

Hi Peter,

I appreciate the code you wrote, however, I still don't think it is the end result I am looking for. I really need to find a way to have just one window open so to not confuse the people using my sheet.

Thanks,

Andrew

 

[Jerry Sullivan]

Andrew, It sounds like Freeze Panes would work for your users except for the fact that it takes up too much screen space.

If the range A2:D18 needs to be visible at all times when a user is on a sheet, then options are someone limited by the constraint that A2:D18 will take up a certain amount of physical space regardless of what tool is used to keep it at the top of the window.

If the range needs to be easily accessible but not always visible, then perhaps you could combine Freezing Panes and using Outline Grouping. You could group rows 2:18 or columns A:D and have nearly full use of the window; then when there is a need to look at the range A2:D18, the user simply clicks the "+" button to expand that data.

 

[andrewb90]

Hi Jerry,

I have some objects with macros on them in that section as well, and it doesn't sound like that will hide and unhide those too easily, but you have given me a great idea!

I have frozen Columns A:D as well as Row 1. In in cell H1 I have a small macro button, that when pressed will unhide Columns A:D, and then rehide them when pressed a second time. This way the info I need is easily accessible, and there is no big frozen section taking up valuable screen space!

Thank you to everybody who tried out some ideas on this!



- Andrew

 

[Peter_SSs]

I have some objects with macros on them in that section as well, and it doesn't sound like that will hide and unhide those too easily, but you have given me a great idea!

I have frozen Columns A:D as well as Row 1. In in cell H1 I have a small macro button, that when pressed will unhide Columns A:D, and then rehide them when pressed a second time. This way the info I need is easily accessible, and there is no big frozen section taking up valuable screen space!

That sounds like a reasonable compromise. Your macro could record where the selection/active cell was when the button was first pressed and return you to that position when it was pressed the second time. That way, if you were down near row 1000, you wouldn't have to spend time scrolling back down there to find your place after viewing A2:D18.

If you did want the be able to see the contents of A2:D18 at all times, another option to consider is relocation/rearranging that data into rows 1:4 instead of columns 1:4 and move your other data down a little. Then you would only need to freeze a few rows which generally doesn't require as much 'real estate' as 4 columns.

And if you really want/need your data in A2:D18, you could mirror those cells horizontally as I have done below.
The formula in F2 is copied across and down.
I assume your other data in columns E:L has a heading row so the window could be frozen above row 7.
I've hidden columns A:D as the information is available at the top of the screen.

Excel Workbook
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
2	A	A2	A3	A4	A5	A6	A7	A8	A9	A10	A11	A12	A13	A14	A15	A16	A17	A18
3	B	B2	B3	B4	B5	B6	B7	B8	B9	B10	B11	B12	B13	B14	B15	B16	B17	B18
4	C	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12	C13	C14	C15	C16	C17	C18
5	D	D2	D3	D4	D5	D6	D7	D8	D9	D10	D11	D12	D13	D14	D15	D16	D17	D18
6	Hdr 1	Hdr 2	Hdr 3	Hdr 4	Hdr 5	Hdr 6	Hdr 7	Hdr 8
7	data	data	data	data	data	data	data	data
8	data	data	data	data	data	data	data	data
9	data	data	data	data	data	data	data	data
10	data	data	data	data	data	data	data	data
11	data	data	data	data	data	data	data	data
12	data	data	data	data	data	data	data	data
Keep Cells Visible