Feb 2 2010 5:57PM GMT
Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba
Setting Column Widths or Hiding Them
Posted by: Craig Hatmaker
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
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
RSS Feed
Email a friend
