Beyond Excel: VBA and Database Manipulation

Feb 2 2010   5:57PM GMT

Setting Column Widths or Hiding Them



Posted by: Craig Hatmaker
Tags:
database
development
excel
Microsoft Excel
ms query
odbc
sql
tutorial
vba
Sometimes we want to include large fields in our results but don’t want them hogging screen realestate.  We can limit those results using the “Width” colum in our Fields Table (See below).  If you couple this with the Format “W” (Wrap), you can display comments and notes without pushing all other data off the screen.
 
Sometimes we want results that are only occasionally used.  We can elect to hide those columns with the Format Table’s Hide column.  If the user wishes to display those rarely used columns, they can use Excel’s Column Unhide or AutoFit commands. 
 
Sometimes we include data in our results required for some calculation, such as aging an invoice, but we don’t care to see the underlying data.  We can hide them as well – and in our next post we will be adding Excel formulas to the Fields Table.
Fields Table with Width and Hide Columns

Fields Table with Width and Hide Columns

Once again, all we need do is add the appropriate columns to our Fields Table (don’t forget to rename it with Shift-Ctrl-N) and sprinkle in a little VBA code.  This time, there are no changes required to Macro1().  We will be changing the Format_Results function only.

Function Format_Results(sDataRange As String, sFieldRange As String) As Boolean
   
'   Format_Range:   Apply numeric formatting or alignment
'   Parameters:     sDataRange  = Range containing data
'                   sFieldRange = Range containing data's field definitions
'   Example:        bResult = Format_Results("Data", "Fields")
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
'   02/02/10 CWH  Added Column Width and Column Hide functionality
   
    Format_Results = Failure    'Assume the worst
    On Error GoTo ErrHandler
   
    Dim s As String
    Dim lColFmt As Long         '"Format" in Fields Table
    Dim lColWid As Long         '"Width"  in Fields Table
    Dim lColHid As Long         '"Hide"   in Fields Table
    Dim lRow As Long
    Dim lRows As Long
   
    lRows = Range(sDataRange).Rows.Count
       
    With Range(sFieldRange)
        lCol = FieldColumn("Format", sFieldRange)
        For lRow = 2 To .Rows.Count 'Start after column header row
            With Range(Range(sDataRange).Cells(1, lRow - 1), _
                       Range(sDataRange).Cells(lRows, lRow - 1))
                'Formatting...
                s = Range(sFieldRange).Cells(lRow, lCol)
                Select Case UCase(s)
                    Case Is = "C"
                        .HorizontalAlignment = xlCenter
                    Case Is = "R"
                        .HorizontalAlignment = xlRight
                    Case Is = "L"
                        .HorizontalAlignment = xlLeft
                    Case Is = "W"
                        .WrapText = True
                    Case Is > ""
                        .NumberFormat = s
                End Select

            End With
            With Range(sDataRange).Columns(lRow -1)
               'Set Column Width ...
                s = Trim(Range(sFieldRange).Cells(lRow, lColWid))
                If Val(s) > 0 Then .ColumnWidth = Val(s)
               'Hide Column...
                s = UCase(Trim(Range(sFieldRange).Cells(lRow, lColHid)))
                If s = "H" Then .EntireColumn.Hidden = True
            End With


        Next
    End With
           
    Format_Results = Success    'Normal end, no errors
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Format_Results - Error#" & Err.Number & vbCrLf & Err.Description, _
            vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function

 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: