Beyond Excel: VBA and Database Manipulation

Jan 18 2010   6:15PM GMT

Preserving Column Headers and Key Values

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

When retrieving large amounts of data, we need to preserve displaying column headers and key values.  Key values should be on the left of your extract.  With that in mind, today we add column “Freeze” to our Fields Table. 

Fields Table with Freeze Column

Fields Table with Freeze Column

“Freeze” tells our routines which columns to keep in view no matter where the user scrolls to.  If you don’t want any, I recommend putting a “Y” in your first field.  This will preserve the column headings at minimum.  You can only indicate one field (or none) to freeze.

Add “Freeze” as shown to your fields table and remember to resize the named range “Fields” by placing your cursor on cell “A3″ and pressing Ctrl-Shift-N.

Below is the “Freeze Panes” routine and a minor modification to our Macro1().

 

Function Freeze_Pane(sDataRange As String, sFieldRange As String) As Boolean
'   Freeze_Pane:        Locks columns and rows from scrolling
'                       (Used to keep column headers and key data displayed)
'   Parameters:         sDataRange  = Range containing data
'                       sFieldRange = Range containing data's field definitions
'   Example:            bResult = Freeze_Pane("Data", "Fields_Data")
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
   
    Freeze_Pane = Failure          'Assume the Worst
    On Error GoTo ErrHandler
    Settings "Save"     'Save current settings
    Settings "Disable"  'Disable events, screen updates, and calculation
       
    Dim lCol As Long
    Dim lRow As Long
   
    Worksheets(Range(sDataRange).Worksheet.Name).Activate
    ActiveWindow.FreezePanes = False
   
    With Range(sFieldRange)
        lCol = FieldColumn("Freeze", sFieldRange)
        For lRow = 2 To .Rows.Count
            If UCase(Trim(.Cells(lRow, lCol))) = "Y" Then
                Range(sDataRange).Cells(2, lRow).Select
                ActiveWindow.FreezePanes = True
            End If
        Next
    End With
    Settings "Restore"     'Restore previous settings
    Freeze_Pane = Success
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Freeze_Pane - Error#" & Err.Number & vbCrLf & Err.Description, _
            vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function 

Modifications to Macro1().
Find the line beginning with If NameExists and replace it with this:

If NameExists("Data") Then
    If Range("Data").Rows.Count > 1 Then
        Freeze_Pane "Data", "Fields"
        Pivot_Template
    End If
End If

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: