Preparing a Forms Functionality | Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))

Preparing a Form’s Functionality

The form now looks good. The next step is to build the functionality for printing the report. You need a way to change between the different row views, and you need a way to hide any unwanted columns. Excel can store different custom views of a work-sheet, which you can then show later as needed. If you build some views into the worksheet, creating a macro to switch between views will be easy.

Create Custom Views on a Worksheet

A custom view allows you to hide rows or columns on a worksheet and then give that view a name so that you can retrieve it easily. You need to create three views. The first view shows all the rows and columns. That one is easy to create. The second view shows only the total rows. The third view shows only the detail rows. Hiding the rows can be a tedious process. Fortunately, you need to hide them only once, and you can use Excel’s Go To Special command to speed up row selection.

Activate Excel. Then on the View tab of the Ribbon, click Custom Views.
In the Custom Views dialog box, click the Add button, type All as the name for the new view, clear the Print settings check box, leave the Hidden rows, columns and filter settings check box selected, and then click OK.

You just created the first view, the one with all rows and columns displayed.

You now need to create the Summary view, showing only the total rows. That means that you need to hide the detail rows, which are the only rows with labels in column B.
Select column B. On the Home tab of the Ribbon, click the Find & Select arrow, and then click Go To Special.
Select the Constants option, and click OK.

Only the cells in the detail rows are still selected.
On the Home tab of the Ribbon, click the Format arrow, point to Hide & Unhide, and click Hide Rows to hide the selected rows.

The remaining rows that you want to hide all have blank cells in column D. Does that give you any ideas?
Select column D. Click the Find & Select arrow, and then click Go To Special.
Select the Blanks option, and click OK. Then hide the selected rows as you did in Step 5.

This is the view for the managers.
To create another view with only the total rows visible, on the View tab of the Ribbon, click Custom Views, click Add, type Summary, clear Print settings, and then click OK.

Now you need to create the detail view, which hides all the summary rows. The rows you want to hide have labels in the range A4:A54.
On the View tab, click Custom Views, and with All selected, click Show to unhide all the rows.
Select the range A4:A54, use Go To Special to select the cells with constants, and then hide the rows.
Select column D, and hide all the rows with blank cells.
With only the detail rows visible, create a new view named Detail, again clearing the Print settings option.
Save the workbook, and try showing each of the three views. Finish with the All view.

Creating the views is bothersome, but you have to do it only once.

Create a Macro to Switch Views

After the views are created, making a macro to switch between views is easy.

Start recording a macro named ShowView.
Show the Summary view, turn off the recorder, and look at the macro.

Ignoring comments, the macro should look like this:
```
Sub ShowView()   ActiveWorkbook.CustomViews("Summary").Show  End Sub 
```
A Workbook object has a collection named CustomViews. You use the name of the view to retrieve a CustomView item from the collection. A CustomView object has a Show method. To switch between views, all you need to do is substitute the name of the view in parentheses. And rather than create three separate macros, you can pass the name of the view as an argument.
Type ViewName between the parentheses after ShowView, and then replace "Summary" (quotation marks and all) with ViewName.

The revised macro should look like this:
```
Sub ShowView(ViewName)   ActiveWorkbook.CustomViews(ViewName).Show  End Sub 
```
Next you’ll test the macro and its argument by using the Immediate window.
Press Ctrl+G to display the Immediate window.

Make sure that the Excel window is visible in the background.
In the Immediate window, type ShowView "Detail" and press Enter.

The worksheet should change to show the detail view.
Type ShowView "All" and press Enter.
Type ShowView "Summary" and press Enter again.

The macro works with all three arguments.
Close the Immediate window, and save the workbook.

You now have the functionality to show different views. Creating the views might not have been fun, but it certainly made writing the macro a lot easier. Also, if you decide to adjust a view (say, to include blank lines), you don’t need to change the macro- changing the view will automatically change the way the macro works.

Dynamically Hide Columns

At this point, you need to create the functionality to hide columns containing dates earlier than the desired starting month. You don’t want to create custom views to change the columns because you would need to create 36 different custom views- one for each of the 12 months times the 3 different row settings. It would be better to change the columns dynamically, based on the choices in the dialog box. If the user chooses to hide columns, you’ll hide from column C to the month that contains the specified month. One good way to find the month is to use Excel’s Find method.

In Excel, select all of row 1, and then start recording a macro named HideMonths.
On the Home tab of the Ribbon, click Find & Select, click Find, and then click the Options button to expand the dialog box.
Type 5/1/2007 in the Find what box, select the Match entire cell contents check box, and make sure the Look in list box says Formulas.

Troubleshooting
If your system uses a date format other than mm/dd/yyyy (the default United States date format), you’ll need to experiment to find the date format that works for you.

When you search for the formula, you look for the underlying date in the cell. The underlying date uses the system date format, regardless of how the cell happens to be formatted. By searching only entire cells, you make sure that 1/1/2007, for example, finds only January (1/1/2007), and not November (11/1/2007), which differs only by the extra digit at the beginning.
Click Find Next, close the Find dialog box, stop the recorder, and then edit the HideMonths macro. Put a line continuation (a space, an underscore, and a new line) after every comma to make the statement readable.

Ignoring comments, the macro should now look like this:
```
Sub HideMonths()   Selection.Find(What:="5/1/2007", _   After:=ActiveCell, _   LookIn:=xlFormulas, _   LookAt:=xlWhole, _   SearchOrder:=xlByRows, _   SearchDirection:=xlNext, _   MatchCase:=False, _   SearchFormat:=False).Activate  End Sub 
```
The macro searches the selection (in this case, row 1) starting with the active cell (in this case, cell A1), searches for the specified date, and activates the matching cell. You don’t want the macro to change the selection, and you don’t want the macro to activate the cell it finds. Rather, you want the macro to assign the cell it finds to a range variable so that you can refer to it.
Make these changes to the macro: Declare the variable myFind as a Range. Change Selection to Rows(1) and ActiveCell to Cells(1). Add Set myFind = to the beginning of the Find statement, and delete .Activate from the end.

The revised macro looks like this:
```
Sub HideMonths()   Dim myFind as Range   Set myFind = Rows(1).Find(What:="5/1/2007", _   After:=Cells(1), _   LookIn:=xlFormulas, _   LookAt:=xlWhole, _   SearchOrder:=xlByRows, _   SearchDirection:=xlNext, _   MatchCase:=False, _   SearchFormat:=False) End Sub 
```
If the Find method is successful, then myFind will contain a reference to the cell that contains the month. You want to hide all the columns from column C (the Rates column) to one column to the left of myFind.
Before the End Sub statement, insert this statement:
```
Range("C1",myFind.Offset(0,-1)).EntireColumn.Hidden = True 
```
This selects a range starting with cell C1 and ending one cell to the left of the cell with the month name. It then hides the columns containing that range.

Save the workbook, and press F8 repeatedly to step through the macro, watching as columns C through H disappear.

You’ll be changing this subroutine to hide columns up to any date. You need some way of knowing whether or not the Find method finds a match. If the Find method does find a match, it assigns a reference to the variable. If it doesn’t find a match,it assigns a special object reference, Nothing, to the variable. You can check to see whether the object is the same as Nothing. Because you’re comparing object references and not values, you don’t use an equal sign to do the comparison. Instead, you use a special object comparison word, Is.

Important

A variable that’s declared as a variant contains the value Empty when nothing else is assigned to it. A variable that’s declared as an object contains the reference Nothing when no other object reference is assigned to it. Empty means “no value,” and Nothing means “no object reference.” To see whether the variable myValue contains the Empty value, use the expression IsEmpty(myValue). To see whether the variable myObject contains a reference to Nothing, use the expression myObject Is Nothing.

Add the If and End If statements around the statement that hides the columns, resulting in this If structure:
```
If Not myFind Is Nothing Then   Range("C1", myFind.Offset(0, -1)) _   .EntireColumn.Hidden = True End If 
```
If the Find method fails, it assigns Nothing to myFind, so the conditional expression is False and no columns are hidden.
Test the macro’s ability to handle an error by changing the value for which the Find method searches from 5/1/2007 to Dog.
Step through the macro, and watch what happens when you get to the If structure. Hold the mouse pointer over the myFind variable and see that its value is Nothing.

If you search for a date that is in row 1, myFind will hold a reference to the cell containing that date, and the macro will hide the months that precede it. If you search for anything else, myFind will hold a reference to Nothing, and the macro won’t hide any columns.
Press F5 to end the macro.

The final step is to convert the date to an argument so that you can search for a month other than May.

Type StartMonth between the parentheses after HideMonths, and replace "5/1/2007" or "Dog" (including the quotation marks) with StartMonth.

The revised (and finished) procedure should look like this:

Sub HideMonths(StartMonth)   Dim myFind As Range   Set myFind = Rows(1).Find(What:=StartMonth, _   After:=Cells(1), _   LookIn:=xlFormulas, _   LookAt:=xlWhole, _   SearchOrder:=xlByRows, _   SearchDirection:=xlNext, _   MatchCase:=False, _   SearchFormat:=False)   If Not myFind Is Nothing Then   Range("C1", myFind.Offset(0, -1)) _   .EntireColumn.Hidden _   = True    End If  End Sub

Now test the macro. Press Ctrl+G to display the Immediate window.
Enter ShowView "All" and then enter HideMonths "8/1/2007".
Close the Immediate window, and save the workbook.

You now have macros that can handle the functionality of the form by hiding appropriate rows and columns. It’s time to put the form and the functionality together.