Free Microsoft Excel 2013 Quick Reference

Freeze frame

I am using a sheet and want to freeze the pane in 2 parts ie at a certain column and at a certain row I can only seem to find a way to freeze it at either the column or row, any ideas ?


Post your answer or comment

comments powered by Disqus
I have a row at the top that I want to freeze frame so I can scroll through the whole document and it will stay and I have some totals at the bottom that I want to be able to see as I scroll through the document, only I can't figure out how.

How can I take a spreadsheet from one worksheet and paste to a new worksheet
but keep all of my columns, rows, and freeze frame set when I paste into the
new worksheet? An exact copy into a new worksheet?

For some reason I can't freeze frames properly. I know how to freeze frames
generally, but whenever I do, it still freezes the first 14 rows instead of
just the top row. I have unprotected the entire worksheet and unfroze the
entire worksheet.

I'm using XP Home with Excel 2000 9.0

Thanks,

Bob

I am trying to get this macro code to function on multiple worksheets. When I select multiple worksheets in a workbook and invoke the macro code it properly freezes the frame on just the first one worksheet but not on the others.

Your troubleshooting assistance is appreciated.

Dell.

Sub FreezeAllPanes()
'
' FreezeAllPanes Macro
'
' Keyboard Shortcut: Ctrl+g
'
        Dim mySheets As Sheets
        Dim actSheet As Worksheet
        Dim wkSht As Worksheet
        Set actSheet = ActiveSheet
        Set mySheets = ActiveWindow.SelectedSheets
        actSheet.Select
        For Each wkSht In mySheets
                ThisWorkbook.Activate
                ActiveWindow.FreezePanes = False
                ActiveWindow.SplitRow = 0
                ActiveWindow.SplitColumn = 0
                ActiveWindow.ScrollColumn = 1
                Range("A1").Select
                ActiveWindow.SmallScroll ToRight:=1
                ActiveWindow.SplitColumn = 3
                ActiveWindow.SmallScroll Down:=7
                ActiveWindow.SplitRow = 1
                ActiveWindow.FreezePanes = True
        Next wkSht
    
End Sub


Hello forum,

I am trying to freeze the top row in two different sheets, but I cannot get it to work. I have tried two different ways. One way works but then when I go back to the sheet at the end of the sub, the top row is unfrozen. Same thing happens for both sheets. The second way gives me a 'Run time error: 438'. Here is the code:


	VB:
	
 
 
Sub FindDuplicates() 
     
     
    On Error Resume Next 
    Application.DisplayAlerts = False 
    Sheets("Pump Duplicates").Delete 
    Sheets("Cylinder Duplicates").Delete 
     
    On Error Goto 0 
    Application.DisplayAlerts = True 
     
     
    If WorksheetExists("Pump Duplicates") = False Then 
        Dim PumpSheet As Worksheet 
        Set PumpSheet = Sheets.Add 
        PumpSheet.Name = "Pump Duplicates" 
    End If 
     
     
    If WorksheetExists("Cylinder Duplicates") = False Then 
        Dim CylinderSheet As Worksheet 
        Set CylinderSheet = Sheets.Add 
        CylinderSheet.Name = "Cylinder Duplicates" 
    End If 
     
     
     '--------------------------
     'Find Pump Duplicates!
     '--------------------------
    Dim n As Long 
    Dim i As Integer 
     
     
    Sheets("All Pumps Data").Activate 
    n = Range("A" & Rows.Count).End(xlUp).Row 
     'Find duplicates from Column A and identify them with a 1 in Column AB
    For i = 1 To n 
        If Application.CountIf(Range("A" & i & ":A" & n), Range("A" & i).Text) > 1 Then 
            Range("AB" & i).Value = 1 
        End If 
    Next i 
     
    Range("A1:AB10000").AutoFilter , Field:=28, Criteria1:=1 'Find the 1's
    Range("A1", Range("A65536").End(xlUp)).EntireRow.Copy 'Copy the duplicates
    PumpSheet.Range("A65536").End(xlUp).Offset(0, 0).PasteSpecial xlPasteValues 'Paste the duplicates
    Selection.AutoFilter 
     
    With Sheets("Pump Duplicates").Cells(1).Resize(1, 26) 'Resize columns
        .Columns.AutoFit 
    End With 
     
    Sheets("Pump Duplicates").Columns(28).Font.Color = RGB(255, 255, 255) '"hide" the 1's from the user
    Sheets("Pump Duplicates").Rows("1:1").FreezePanes = True 'ERROR OCCURS ON THIS LINE
     
     'Range("A1").Select
     'ActiveWindow.FreezePanes = True 'Also tried this. It will freeze the row but then it unfreezes some how.
     
     
     '--------------------------
     'Find Cylinder Duplicates!
     '--------------------------
     
     
    Sheets("All Cylinders Data").Activate 
    n = Range("A" & Rows.Count).End(xlUp).Row 
     'Find duplicates from the list.
    For i = 1 To n 
        If Application.CountIf(Range("A" & i & ":A" & n), Range("A" & i).Text) > 1 Then 
            Range("AB" & i).Value = 1 
        End If 
    Next i 
     
    Range("A1:AB10000").AutoFilter , Field:=28, Criteria1:=1 
    Range("A1", Range("A65536").End(xlUp)).EntireRow.Copy 
    CylinderSheet.Range("A65536").End(xlUp).Offset(0, 0).PasteSpecial xlPasteValues 
    Selection.AutoFilter 
     
    With Sheets("Cylinder Duplicates").Cells(1).Resize(1, 26) 
        .Columns.AutoFit 
    End With 
     
    Sheets("Cylinder Duplicates").Columns(28).Font.Color = RGB(255, 255, 255) 
    Sheets("Pump Duplicates").Range("A1").FreezePanes = True 
     
     
     'Range("A1").Select
     'ActiveWindow.FreezePanes = True
     
     
    Sheets("Pump Duplicates").Select 
    Range("A1").Select 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for your help!

Ok, its my turn to ask a question.
When an Excel file is opened via the intranet or browser all controls, menus and macos work but the Window menu does not show.
Windows-Freeze Frames or Windows-Split canot be used.
The end-user prefers opening the file over the intranet. I think I could add a VBA freeze-frame button w/code that would work on the web and will test that out.

Does anyone have any insights, comments or non-code solution?

Thx, Doug

I have several macros and a freeze frame line between lines 3 and 4 in an Excel sheet. The macro buttons are across lines 1 and 2. When I activate a macro having 2 or 3 sorts the freeze frame line disappears and the data moves up to line 1 and 2 under the buttons. Did I do something wrong. How can I fix this?

How do I set via code the Windows Freeze frame at C9 and have the
cursor on Cell C9 also

Thanks

Like SCA, I believe this is a shared file (with filters) issue, since it
doesn't seem to happen on non-shared files. Here's the problem:

I have a habit of freezing the top two rows in my workbooks (with multiple
worksheets) so that column headings in row 1 and filters in row 2 are always
visible when I scroll down. In shared files only, the freeze frame will have
mysteriously jumped down to another row, for example row 100, when I first
open the file. In other words, when I first open the worksheet, I might see
rows 1 to 30 on my screen, but I can't scroll down because the freeze frame
is down at row 100. This can happen at odd times on all worksheets in a
workbook, or just some of them. So unfortunately, I often end up having to
reset the freeze pane under row 2 again for all or some of the worksheets.
Since there are only two of us in the office sharing these workbooks, I'm
confident that neither of us are inadvertantly changing the placement of the
frozen panes. Perhaps the fact that we are both filtering columns and saving
the shared workbooks is causing Excel to reset the freeze pane inadvertantly.

If someone could figure out this problem it would be very helpful to me.
Shared workbooks are a powerful tool but I have to limit there use to just a
few power users, sothis problem really limits my ability to use shared
workbooks more broadly in my office.

"Dave Peterson" wrote:

> There's a guy at work who sometimes applies Window|Freeze panes to a filtered
> list.
>
> While the list is filtered the way he has it, you can see the titles and the
> data nicely.
>
> But when you do Data|filter|show all (or reset the filter), the cell used to set
> the freeze panes is now off the viewable screen and it looks like the display
> won't move.
>
> An example.
>
> He applies data|filter|autofilter to his data. Filters on a value in column A.
> You can see sell A1 (headers) and then A65. He selects A65 (the first visible
> cell under the header) and applies window freeze panes.
>
> But when someone shows all the data, Rows 1 to 64 are frozen. And the screen
> looks frozen when scrolling around.
>
> Removing the freeze panes and reapplying when the filter isn't engaged returns
> things to normal.
>
> Maybe it's something as simple as that -- and nothing to do with the
> "sharedness" of the workbook at all.
>
> SCA wrote:
> >
> > We are using a Shared file, but the Freeze panes at
> > times "resets" so that it appears the screen is frozed
> > unitl you reset freeze panes.
> >
> > This is not due to user changesm but occurs in the
> > original file that was emailed.
> >
> > Is ther any way to prevent this?
>
> --
>
> Dave Peterson
>

when i chose freeze frame from the windows menu it freeez the first 17 rows.
how do i get it to reeze row 1 only ?
--
huggy

HOW do I print A frame that is frozen, to where it would print on each page.

This is a follow-up to SCA's post of 10/21/04. Like SCA, I have a freeze pane
problem in shared Excel 200 files that have autofilter enabled. Here's the
problem:

I have a habit of freezing the top two rows in all of the worksheets in a
shared workbook so that column headings in row 1 and filters in row 2 are
always
visible when I scroll down. In shared files only, the freeze frame will have
mysteriously jumped down to another row, for example row 100, when I first
open a file. In other words, when I first open the worksheet, I might see
rows 1 to 30 on my screen, but I can't scroll down because the freeze frame
is down at row 100. This can happen at odd times on all worksheets in a
workbook, or just some of the worksheets in a workbook. So unfortunately, I
often end up having to reset the freeze pane at row 2 again for all or some
of the worksheets.

Since there are only two of us in the office sharing these workbooks, I'm
confident that neither of us are changing the placement of the
frozen panes. Perhaps the fact that we are both filtering columns and saving
the shared workbooks is causing Excel to reset the freeze pane inadvertantly.

If someone could figure out this problem it would be very helpful to me.
Shared workbooks are a powerful tool, but this problem really limits my
ability to use shared workbooks more broadly in my office.

--
JM

Hello to all,

I have created a worksheet to track/calculate compliance with a number of important items at a number of business locations on a number of questions. This workbook will be posted on a central server that will allow 25+ employees to access it from the field and "double-click" to enter an X in either a "Yes" or "No" column for about 100+ locations (the code also will remove the "X" in either column if another "X" is entered into the other column to prevent double entries). The workbook will be quite elaborate and will be locked down except for those cells unprotected for data entry (identifying location, date, etc.).

Below is the code and it works just fine as written with two columns to chose from to "double-click" and enter an "X" (columns C & D for rows 4 thru 15). I want to "freeze frames" locking columns A & B and allow scrolling to the right to enter each New Location and allow the same code to apply to the same rows but column groupings of E & F, G & H, I & J, ...until I have about 100+ sets of two columns identified in the code.

I have tried a variety of changes to the "Union(Range" and "Intersect(Target, Range" last night and this morning to no avail and have gotten nothing but a headache and a desire to drink heavily from the exercise.

Can someone give me some suggestions?


	VB:
	
 
Private Sub Worksheet_BeforeDoubleClick( _ 
    ByVal Target As Range, Cancel As Boolean) 
    Dim rInt As Range 
    Dim rCell As Range 
     
    Set rInt = Intersect(Target, Range("C4:D15")) 
    If Not rInt Is Nothing Then 
        For Each rCell In rInt 
            rCell.Value = "X" 
        Next 
    End If 
    Set rInt = Nothing 
    Set rCell = Nothing 
    Cancel = True 
     
End Sub 
Public Sub Worksheet_Change(ByVal Target As Range) 
    On Error Resume Next 
    Application.ScreenUpdating = False 
    Dim rs As Range 
    Dim cl As String 
    cl = Target.Value 
    Set rs = Union(Range("C" & Target.Row), Range("D" & Target.Row)) 
    Set isect = Intersect(Target, rs) 
    If Not isect Is Nothing Then 
        If Target.Value = "X" Then 
            Worksheets.Application.EnableEvents = False 
            rs.ClearContents 
            Let Target = cl 
        End If 
    End If 
    Worksheets.Application.EnableEvents = True 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you.

Is there a way to set a macro that will highlight the entire ROW in a colour when I select a CELL, so that it is easy for the user to identify the information.

I have a very large worksheet and I am currently using freeze frame, but it still becomes a bit confusing as there is a lot of information on the sheet to look at.

e.g. if I was in column J cell 15 then the whole ROW would change colour to blue

If this is possible, then I would like it to start from Cell 9 and end for Cell 33

Cheers

Hi!

I am trying to set up a worksheet where I have different columns for each
area in a budget (ie "Food", "entertainment", "security", etc)

Column A&B = Date and decription of each item
Row 1: Budget areas listed above

Now I have done the "freeze frame" command so that no matter where I scroll,
I ca always see Row #1. I would like to have a running balance for each
column that I could also see no matter where in the worksheet I am.

For Example, C1 = "Food"
C2 = a running balance for how much is left in the food budget
C3 - C(really big number) would be where I enter money spent on food, or
donations to the food budget, and C2 would automatically update to tell me
the current balance

I hope I explained this okay. I'm not married to the idea of setting it up
exactly like this, if there is a better way. I just want to be able to have a
running balance for each budget area that I can easily see without having to
scroll all over the place.

Thanks in advance!

In a spreadsheet an ActiveX control button has lost its original
appearance. Originally it looked like a nice rectangle with some text
inside it.
It was located at the top of a spreadsheet (around cell L1). There is
another control button at the top of the same sheet (around cell E1) -
but this one isn't ActiveX, it's created from the form menu and is
linked to a macro.

I used Freeze Frame on cell C5 and noticed a slow degradation in the
appearance of the ActiveX control button as the sheet was scrolled to
the far right.

Anyone have any ideas how to restore the appearance of the ActiveX
control button and how to keep it from changing appearance?

Let me try to describe the current appearance of the ActiveX control
button: text is no longer visible inside the area of the button; the
button's edges are faint; the button is comprised of some seemingly
randomly placed rectangles; the rectangles are different shades of
gray.

Thanks for any suggestions!

Mark

Excuse my ignorance – old retired sod and used Lotus since the PC was hatched!
Have some spreadsheets set up for my wife and worked well but now the arrow
keys (on the keyboard) won’t move the cursor – they will move the page up and
down.
Can only move the cursor with the mouse.
Freeze frames are off.
Am not aware of any settings having been changed.
Try the spreadsheets out on my PC and they are fine.
Appreciate any assistance

--
Why work on Wednesday and stuff up two good long weekends

Hi!

I am trying to set up a worksheet where I have different columns for each
area in a budget (ie "Food", "entertainment", "security", etc)

Column A&B = Date and decription of each item
Row 1: Budget areas listed above

Now I have done the "freeze frame" command so that no matter where I scroll,
I ca always see Row #1. I would like to have a running balance for each
column that I could also see no matter where in the worksheet I am.

For Example, C1 = "Food"
C2 = a running balance for how much is left in the food budget
C3 - C(really big number) would be where I enter money spent on food, or
donations to the food budget, and C2 would automatically update to tell me
the current balance

I hope I explained this okay. I'm not married to the idea of setting it up
exactly like this, if there is a better way. I just want to be able to have a
running balance for each budget area that I can easily see without having to
scroll all over the place.

Thanks in advance!

I have a bunch of validation comboboxes on this excel spreadsheet. Because I
have the columns so small, it will not show the full length. Is there a way
for it to automatically expand?

Or is there a way to get the cells in a freeze frame to not be affected by
the resize of a column although it's in the same column?

I used to be able to use the arrow keys to move around cells but now they
scroll the page. This is happening in files that don't have freeze frames and
ones that do.
I have to place the cursor with my mouse.
Is there a setting that inadvertantly got set?
Thanks

I have been given a very large spreadsheet with alot of data. I need the
column titles to print on every page. The freeze frames make it easy to read
all the data on the computer, but when printed off, the first page needs to
be near by to see what data is in each column. How would i go about making
rows 1 to 9 appear on EVERY printed page?

I want to keep either a Row or Column Static (A:A) or (A1:IV1)

I know the freeze frame does the trick, but when I zoom in or out the Row or Column also changes in size. But I want to keep the same height of the row or same the width of the column.

Is that possible?

Thanks

Panic

Is there a way to have a spreadsheet automatically repeat the header every 25
rows without using the Freeze Frame?

Reason: the users of some huge spreadsheets need the header repeated for
expediency in looking things up and troubleshooting entries.

I have some combo and text boxes at the top of file. Based on the selection
of the combo I change the data displayed below this area. When I scroll down
the data the combo and text boxes scroll off also.

I want to be able to scroll through the data below and keep the combo boxes
floating on top (like as in freezing frames). I've tried to mess with
protecting the the sheet in combination with locking the objects, but that
stops me from editing the combi itself.

Any input would be greatly appreciated.


No luck finding an answer? You could always try Google.