How the macro works

When you read through the lines of the code, you'll see that this macro is relatively intuitive:

Sub Macro1()

'Step 1: Tell Excel what to do if error
    On Error GoTo MyError

'Step 2: Add a sheet and name it
    Sheets.Add
    ActiveSheet.Name = _
    WorksheetFunction.Text(Now(), "m-d-yyyy h_mm_ss am/pm")
    Exit Sub

'Step 3: If here, an error happened; tell the user
    MyError:
    MsgBox "There is already a sheet called that."

End Sub

You must anticipate that if you give the new sheet a name that already exists, an error would occur. So in Step 1, the macro tells Excel to immediately skip to the line that says MyError (in Step 3) if there is an error.

Step 2 uses the Add method to add a new sheet. By default, the sheet is called Sheetxx, where xx represents the number of the sheet. You give the sheet a new name by changing the Name property of the ActiveSheet object. In this case, you're naming the worksheet with the current date and time.

As with workbooks, each time you use VBA to add a new sheet, the newly added sheet automatically becomes the active sheet. Finally, in Step 2, note that the macro exits the procedure. It has to do this so that it doesn’t accidentally go into Step 3 (which comes into play only if an error occurs).

Step 3 notifies the user that the sheet name already exists. Again, this step should be activated only if an error occurs.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module.

How the macro works

The macro in this section loops through the worksheets, matching each worksheet name to the active sheet’s name. Each time the macro loops, it deletes any unmatched worksheet. Note the use of the DisplayAlerts property in Step 4. This effectively turns off Excel’s warnings so you don’t have to confirm each delete.

Sub Macro1()

'Step 1: Declare your variables
    Dim ws As Worksheet

'Step 2: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets

'Step 3: Check each worksheet name
    If ws.Name <> ThisWorkbook.ActiveSheet.Name Then

'Step 4: Turn off warnings and delete
    Application.DisplayAlerts = False
    ws.Delete
    Application.DisplayAlerts = True
    End If

'Step 5: Loop to next worksheet
    Next ws

End Sub

The macro first declares an object called ws. This step creates a memory container for each worksheet it loops through.

In Step 2, the macro begins to loop, telling Excel it will evaluate all worksheets in this workbook. There is a difference between ThisWorkbook and ActiveWorkbook. The ThisWorkBook object refers to the workbook that contains the code. The ActiveWorkBook object refers to the currently active workbook. They often return the same object, but if the workbook running the code is not the active workbook, they return different objects. In this case, you don’t want to risk deleting sheets in other workbooks, so you use ThisWorkBook.

In Step 3, the macro simply compares the active sheet name to the sheet that is currently being looped.

In Step 4, if the sheet names are different, the macro deletes the sheet. As mentioned, you use DisplayAlerts to suppress any confirmation checks from Excel. If you want to be warned before deleting the sheets, you can omit Application. DisplayAlerts = False. Omitting the DisplayAlerts statement will ensure that you get the message in Figure 5-1, allowing you to back out of the decision to delete worksheets.

In Step 5, the macro loops back to get the next sheet. After all the sheets are evaluated, the macro ends.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module.

When you use ThisWorkbook instead of ActiveWorkbook in a macro, you can’t run the macro from the personal macro workbook. Why? Because ThisWorkbook would refer to the personal macro workbook, not to the workbook to which the macro should apply.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module.

How the macro works

This macro loops through the worksheets and changes their visible state.

Sub Macro1()

'Step 1: Declare your variables
    Dim ws As Worksheet

'Step 2: Start looping through all worksheets
    For Each ws In ActiveWorkbook.Worksheets

'Step 3: Loop to next worksheet
    ws.Visible = xlSheetVisible
    Next ws

End Sub

Step 1 declares an object called ws. This step creates a memory container for each worksheet that the macro loops through.

In Step 2, the macro starts the looping, telling Excel to enumerate through all worksheets in this workbook.

Step 3 changes the visible state to xlSheetVisible. Then it loops back to get the next worksheet.

How to use the macro

The best place to store this macro is in your personal macro workbook. That way, the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In the VBE project window, it is named personal.xlsb.

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click personal.xlb in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module.

If you don’t see personal.xlb in your project window, the file doesn't exist yet. You’ll have to record a macro using personal macro workbook as the destination.

To record the macro in your personal macro workbook, display the Record Macro dialog box before you start recording. Then click the Store Macro In drop-down box and select the Personal Macro Workbook option. Simply record a few cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.

How the macro works

When you want to rearrange sheets, you use the Move method of either the Sheets object or the ActiveSheet object. When using the Move method, you specify where to move the sheet to by using the After argument, the Before argument, or both.

Sub Macro1()

'Move the active sheet to the end
    ActiveSheet.Move After:=Worksheets(Worksheets.Count)

'Move the active sheet to the beginning
    ActiveSheet.Move Before:=Worksheets(1)

'Move Sheet 1 before Sheet 12
    Sheets("Sheet1").Move Before:=Sheets("Sheet12")

End Sub

This macro demonstrates how to move the active worksheet to three locations:

• Move the active sheet to the end: When you need to move a worksheet to the end of the workbook, you essentially want to tell Excel to move the sheet after the last sheet. But there's no code in VBA that lets you point to the last sheet. However, you can find the maximum count of worksheets, and use that number as an index for the Worksheets object. For example, you could enter Worksheets(1) to point to the first sheet in a workbook, and enter Worksheet(3) to point to the third sheet in the workbook. To point to the last sheet in the workbook, you could replace the index number with the Worksheets.Count property. Worksheets.Count will give you the total number of worksheets, which will always be the same number as the index for the last sheet. Thus Worksheet(Worksheets.Count) will point to the last sheet.
• Move the active sheet to the beginning: Moving a sheet to the beginning of the workbook is simple. Use Worksheets(1) to point to the first sheet in the workbook, and then move the active sheet before that one.
• Move Sheet 1 before Sheet X: You can also move a sheet before or after another sheet simply by calling that other sheet out by name. In the example demonstrated in the preceding macro, you are moving Sheet1 before Sheet12.

How to use the macro

The best place to store this kind of a macro is in your personal macro workbook so that the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In the VBE project window, it is named personal.xlsb.

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click personal.xlb in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module.

If you don’t see personal.xlb in your project window, the file doesn't exist yet. You’ll have to record a macro, using personal macro workbook as the destination.

To record the macro in your personal macro workbook, display the Record Macro dialog box before you start recording. Then click the Store Macro In drop-down box and select the Personal Macro Workbook option. Simply record a few cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.

How the macro works

The macro in this section looks more complicated than it is. The macro simply iterates through the sheets in the workbook, comparing the current sheet to the previous one. If the name of previous sheet is greater than the current sheet (alphabetically), the macro moves the current sheet before it. By the time all the iterations are completed, you have a sorted workbook!

Sub Macro1()

'Step 1: Declare your variables
    Dim CurrentSheetIndex As Integer
    Dim PrevSheetIndex As Integer

'Step 2: Set the starting counts and start looping
    For CurrentSheetIndex = 1 To Sheets.Count
    For PrevSheetIndex = 1 To CurrentSheetIndex - 1

'Step 3: Check current sheet against previous sheet
    If UCase(Sheets(PrevSheetIndex).Name) > _
       UCase(Sheets(CurrentSheetIndex).Name) Then

'Step 4: Move if current sheet comes before previous sheet
    Sheets(CurrentSheetIndex).Move _
    Before:=Sheets(PrevSheetIndex)
    End If

'Step 5 Loop back to iterate again
    Next PrevSheetIndex
    Next CurrentSheetIndex

End Sub

Note that this technique performs text-based sorting, so you may not get the results you were expecting when working with number-based sheet names. For instance, Sheet10 will come before Sheet2 because textually, 1 comes before 2. Excel can't do number-based sorting (in which 2 would come before 10).

Step 1 declares two integer variables. The CurrentSheetIndex variable holds the index number for the current sheet iteration, and the PrevSheetIndex variable holds the index number for the previous sheet iteration.

In Step 2, the macro starts iteration counts for both variables. Note that the count for PrevSheetIndex is one number behind CurrentSheetIndex. After the counts are set, you start looping.

In Step 3, you check to see whether the name of the previous sheet is greater than that of the current sheet. Note the UCase function, which you use to get both names in the same uppercase state. This function prevents sorting errors due to different case states.

Step 4 is reached only if the previous sheet name is greater than the current sheet name. In this step, you use the Move method to move the current sheet before the previous sheet.

In Step 5, you go back around to the start of the loop. Every iteration of the loop increments both variables up one number until the last worksheet is touched. After all iterations have completed, the macro ends.

How to use the macro

The best place to store this macro is in your personal macro workbook so that the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In the VBE project window, it is named personal.xlsb.

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click personal.xlb in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module.

If you don’t see personal.xlb in your project window, the file doesn't exist yet. You’ll have to record a macro, using personal macro workbook as the destination.

To record the macro in your personal macro workbook, display the Record Macro dialog box before you start recording. Then click the Store Macro In drop-down box and select the Personal Macro Workbook option. Simply record a few cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.

How the macro works

You may think it's impossible to sort or group by color, but Excel offers a way. Excel assigns an index number to every color. A light yellow color may have an index number of 36, whereas a maroon color has the index number 42.

This macro iterates through the sheets in the workbook, comparing the tab color index of the current sheet to that of the previous one. If the previous sheet has the same color index number as the current sheet, the macro moves the current sheet before it. By the time all the iterations are completed, all sheets are grouped based on their tab color.

Sub Macro1()

'Step 1: Declare your variables
    Dim CurrentSheetIndex As Integer
    Dim PrevSheetIndex As Integer

'Step 2: Set the starting counts and start looping
    For CurrentSheetIndex = 1 To Sheets.Count
    For PrevSheetIndex = 1 To CurrentSheetIndex - 1

'Step 3: Check current sheet against previous sheet
    If Sheets(PrevSheetIndex).Tab.ColorIndex = _
    Sheets(CurrentSheetIndex).Tab.ColorIndex Then

'Step 4: Move if current and previous color indexes match Sheets(PrevSheetIndex).Move _
    Before:=Sheets(CurrentSheetIndex)
    End If

'Step 5 Loop back to iterate again
    Next PrevSheetIndex
    Next CurrentSheetIndex

End Sub

Step 1 declares two integer variables. The CurrentSheetIndex variable holds the index number for the current sheet iteration, and the PrevSheetIndex variable holds the index number for the previous sheet iteration.

Step 2 starts iteration counts for both variables. Note that the count for PrevSheetIndex is one number behind CurrentSheetIndex. After the counts are set, the macro starts looping.

In Step 3, the macro checks to see whether the color index of the previous sheet is the same as that of the current sheet. Note the use of the Tab.ColorIndex property.

Step 4 is reached only if the color index of the previous sheet is equal to the color index of the current sheet. In this step, the macro uses the Move method to move the current sheet before the previous sheet.

In Step 5, the macro goes back to the start of the loop. Every iteration of the loop increments both variables up one number until the last worksheet is touched. After all of the iterations have run, the macro ends.

How to use the macro

The best place to store this macro is in your personal macro workbook so that the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In the VBE project window, it is named personal.xlsb.

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click personal.xlb in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module.

If you don’t see personal.xlb in your project window, the file doesn't exist yet. You’ll have to record a macro, using personal macro workbook as the destination.

To record the macro in your personal macro workbook, display the Record Macro dialog box before you start recording. Then click the Store Macro In drop-down box and select the Personal Macro Workbook option. Simply record a few cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.

How the macro works

In this macro, the active sheet is first copied. Then you use the Before parameter to send the copy to a new workbook that is created on the fly. The copied sheet is positioned as the first sheet in the new workbook.

The use of the ThisWorkbook object is important here. It ensures that the active sheet that is being copied is from the workbook that contains the code, not from the newly created workbook.

Sub Macro1()

'Copy sheet, and send to new workbook
    ThisWorkbook.ActiveSheet.Copy _
    Before:=Workbooks.Add.Worksheets(1)

End Sub

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module.

How the macro works

In this macro, you are looping through the worksheets, copying each sheet, and then sending the copy to a new workbook that is created on the fly. The thing to note here is that the newly created workbooks are saved in the same directory as your original workbook, with the same filename as the copied sheet (wb.SaveAs ThisWorkbook.Path & "" & ws.Name).

Sub Macro1()

'Step 1: Declare your variables
    Dim ws As Worksheet
    Dim wb As Workbook

'Step 2: Start looping through sheets
    For Each ws In ThisWorkbook.Worksheets

'Step 3: Create new workbook and save it
    Set wb = Workbooks.Add
    wb.SaveAs ThisWorkbook.Path & "" & ws.Name

'Step 4: Copy the target sheet to the new workbook
    ws.Copy Before:=wb.Worksheets(1)
    wb.Close SaveChanges:=True

'Step 5: Loop back to the next worksheet
    Next ws

End Sub

Not all valid worksheet names translate to valid filenames.

Windows has specific rules regarding filenames. You can't use these characters when naming a file: backslash (), forward slash (/), colon (:), asterisk (*), question mark (?), pipe (|), double quote (“), greater than (>) and less than (<).

The twist is that you can use a few of these restricted characters in your sheet names; specifically, double quote, pipe, greater than, and less than. So, as you run this macro, naming the newly created files to match the sheet name may cause an error. For instance, the macro will throw an error if you try to create a new file from a sheet called May|Revenue (because of the pipe character).

Step 1 declares two object variables. The ws variable creates a memory container for each worksheet through which the macro loops. The wb variable creates the container for the new workbooks you create.

In Step 2, the macro starts looping through the sheets. The use of the ThisWorkbook object ensures that the active sheet that is being copied is from the workbook containing the code, not from the new workbook that is created.

In Step 3, you create the new workbook and save it. You save this new book in the same path as the original workbook (ThisWorkbook). The filename is set to the same name as the currently active sheet.

Step 4 copies the currently active sheet and uses the Before parameter to send it to the new book as the first tab.

Step 5 loops back to get the next sheet. After all sheets have been evaluated, the macro ends.

This macro will not work on a workbook that has not been initially saved — that is to say, saved at least one time.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click the project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module.

How the macro works

This macro is easy. All you have to do is pass in an array the sheets you want printed, and then you use the PrintOut method to trigger the print job. All the sheets you have entered are printed in one go.

Sub Macro1()

'Print certain sheets
    ActiveWorkbook.Sheets( _
    Array("Sheet1", "Sheet3", "Sheet5")).PrintOut Copies:=1

End Sub

Want to print all worksheets in a workbook? The following macro is even easier:

Sub Macro1()

'Print all sheets
    ActiveWorkbook.Worksheets.PrintOut Copies:=1

End Sub

How to use the macro

The best place to store this macro is in your personal macro workbook so that the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In the VBE project window, it is named personal.xlsb.

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click personal.xlb in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module.

If you don’t see personal.xlb in your project window, the file doesn't exist yet. You’ll have to record a macro using Personal Macro Workbook as the destination.

To record the macro in your personal macro workbook, display the Record Macro dialog box before you start recording. Then click the Store Macro In drop-down box and select the Personal Macro Workbook option t. Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.

How the macro works

In this macro, you loop through the worksheets and simply apply protection with a password. The Password argument defines the password needed to remove the protection. The Password argument is optional. If you omit it, the sheet will still be protected; you just won’t need to enter a password to unprotect it.

Excel passwords are case-sensitive, so you’ll want pay attention to the exact capitalization you are using in your macro.

Sub Macro1()

'Step 1: Declare your variables
    Dim ws As Worksheet

'Step 2: Start looping through all worksheets
    For Each ws In ActiveWorkbook.Worksheets

'Step 3: Protect and loop to next worksheet
    ws.Protect Password:="RED"
    Next ws

End Sub

Step 1 declares an object called ws. This step creates a memory container for each worksheet you loop through.

Step 2 starts the looping, telling Excel that you want to enumerate through all worksheets in this workbook.

In Step 3, the macro applies protection with the given password, and then loops back to get the worksheet.

How to use the macro

The best place to store this macro is in your personal macro workbook so that the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In the VBE project window, it is named personal.xlsb.

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click personal.xlb in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module.

If you don’t see personal.xlb in your project window, the file doesn't exist yet. You’ll have to record a macro using Personal Macro Workbook as the destination.

To record the macro in your personal macro workbook, display the Record Macro dialog box before you start recording. Then click the Store Macro In drop-down box and select the Personal Macro Workbook option. Then simply record a few cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.

How the macro works

The macro loops through the worksheets and uses the Password argument to unprotect each sheet:

Sub Macro1()

'Step 1: Declare your variables
    Dim ws As Worksheet

'Step 2: Start looping through all worksheets
    For Each ws In ActiveWorkbook.Worksheets
'Step 3: Loop to next worksheet
    ws.UnProtect Password:="RED"
    Next ws

End Sub

Step 1 declares an object called ws. This step creates a memory container for each worksheet you loop through.

Step 2 starts the looping, telling Excel to enumerate through all worksheets in this workbook.

Step 3 unprotects the active sheet, providing the password as needed, and then loops back to get the worksheet.

The assumption is that all worksheets that need to be unprotected have the same password. If this not the case, you need to explicitly unprotect each sheet with its corresponding password:

Sub Macro1()

Sheets("Sheet1").UnProtect Password:="RED"
Sheets("Sheet2").UnProtect Password:="BLUE"
Sheets("Sheet3").UnProtect Password:="YELLOW"
Sheets("Sheet4").UnProtect Password:="GREEN"

End Sub

How to use the macro

The best place to store this kind of a macro is in your personal macro workbook so that the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In the VBE project window, it will be named personal.xlsb.

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click personal.xlb in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module.

If you don’t see personal.xlb in your project window, the file doesn't exist yet. You’ll have to record a macro, using Personal Macro Workbook as the destination.

To record the macro in your personal macro workbook, display the Record Macro dialog box before you start recording. Then click the Store Macro In drop-down box and select the Personal Macro Workbook option. Simply record a few cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.

How the macro works

It’s easy to get intimidated when looking at the macro in this section because a lot is going on. However, if you step back and consider the few simple actions it does, it becomes less scary. The macro

• Removes any previous Table of Contents sheet
• Creates a new Table of Contents sheet
• Grabs the name of each worksheet and pastes it to the table of contents
• Adds a hyperlink to each entry in the table of contents

That doesn’t sound so bad. Now look at the code:

Sub Macro1()

'Step 1: Declare your variables
    Dim i As Long

'Step 2: Delete previous TOC if it exists
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Table Of Contents").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

'Step 3: Add a new TOC sheet as the first sheet
    ThisWorkbook.Sheets.Add _
    Before:=ThisWorkbook.Worksheets(1)
    ActiveSheet.Name = "Table Of Contents"

'Step 4: Start the i counter
    For i = 1 To Sheets.Count

'Step 5: Select next available row
    ActiveSheet.Cells(i, 1).Select

'Step 6: Add sheet name and hyperlink
    ActiveSheet.Hyperlinks.Add _
    Anchor:=ActiveSheet.Cells(i, 1), _
    Address:="", _
    SubAddress:="'" & Sheets(i).Name & "'!A1", _
    TextToDisplay:=Sheets(i).Name

'Step 7: Loop back and increment i
    Next i

End Sub

Step 1 declares an Integer variable called i to serve as the counter as the macro iterates through the sheets.

Note that this macro is not looping through the sheets the way previous macros in this chapter did. In previous macros, you looped through the Worksheets collection and selected each worksheet there. In this procedure, you use a counter (your i variable). The main reason is because you have to not only keep track of the sheets but also enter each sheet name on a new row in a table of contents. The idea is that as the counter progresses through the sheets, it also serves to move the cursor down in the table of contents so each new entry goes on a new row.

Step 2 essentially attempts to delete any previous sheet called Table of Contents. Because there may not be any Table of Contents sheet to delete, you have to start Step 2 with the On Error Resume Next error handler. This handler tells Excel to continue the macro if an error is encountered here. You then delete the Table of Contents sheet using the DisplayAlerts method, which effectively turns off Excel’s warnings so you don’t have to confirm the deletion. Finally, you reset the error handler to trap all errors again by entering On Error GoTo 0.

In Step 3, you add a new sheet to the workbook by using the Before argument to position the new sheet as the first sheet. You then name the sheet Table of Contents. As mentioned, when you add a new worksheet, it automatically becomes the active sheet. Because this new sheet has the focus throughout the procedure, any references to ActiveSheet in this code refer to the Table of Contents sheet.

Step 4 starts the i counter at 1 and ends it at the maximum count of all sheets in the workbook. Again, instead of looping through the Worksheets collection like you did in previous macros, you simply use the i counter as an index number that you can pass to the Sheets object. When the maximum number is reached, the macro ends.

Step 5 selects the corresponding row in the Table of Contents sheet. That is to say, if the i counter is on 1, it selects the first row in the Table of Contents sheet. If the i counter is at 2, it selects the second row, and so on.

You select rows by using the Cells item, which provides a handy way of selecting ranges through code. It requires only relative row and column positions as parameters. So Cells(1,1) translates to row 1, column 1 (or cell A1). Cells(5, 3) translates to row 5, column 3 (or cell C5). The numeric parameters in the Cells item are particularly useful when you want to loop through a series of rows or columns by using an incrementing index number.

Step 6 uses the Hyperlinks.Add method to add the sheet name and hyperlinks to the selected cell. This step feeds the Hyperlinks.Add method the parameters it needs to build out the hyperlinks.

The last step in the macro loops back to increment the i counter to the next count. When the i counter reaches a number that equals the count of worksheets in the workbook, the macro ends.

How to use the macro

To implement this macro, you can copy and paste it into a standard module:

1. Activate Visual Basic Editor by pressing Alt+F11.
2. Right-click project/workbook name in the project window.
3. Choose Insert  ⇒  Module.
4. Type or paste the code in the newly created module.

How the macro works

With this macro in place, you can double-click a cell in the spreadsheet to zoom in 200 percent. Double-click again and Excel zooms back to 100 percent. You can change the values and complexity in the code to fit your needs.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

'Check current zoom state
'Zoom to 100% if at 100
'Zoom 200% if currently at 100
    If ActiveWindow.Zoom <> 100 Then
    ActiveWindow.Zoom = 100
    Else
    ActiveWindow.Zoom = 200
    End If

End Sub

The side effect of double-clicking a cell is that it goes into edit mode. You can exit edit mode by pressing the escape key (Esc). If you find it annoying to keep pressing Esc when triggering this macro, add the following statement to the end of the procedure:

Application.SendKeys ("{ESC}")

This statement mimics an Esc keypress.

How to use the macro

To implement this macro, you need to copy and paste it into the Worksheet_BeforeDoubleClick event code window. Placing the macro there allows it to run each time you double-click the sheet.

1. Activate Visual Basic Editor by pressing Alt+F11.
2. In the project window, find your project/workbook name and click the plus sign next to it to see all the sheets.
3. Click the sheet from which you want to trigger the code.
4. In the Event drop-down list, select the BeforeDoubleClick event (see Figure 5-8).
5. Type or paste the code in the newly created module.

How the macro works

Take a look at how this macro works:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

'Step 1: Declare your variables
    Dim strRange As String

'Step 2: Build the range string
    strRange = Target.Cells.Address & "," & _
               Target.Cells.EntireColumn.Address & "," & _
               Target.Cells.EntireRow.Address

'Step 3: Pass the range string to a range
    Range(strRange).Select

End Sub

In Step 1, you first declare an object called strRange. This step creates a memory container you can use to build a range string.

A range string is nothing more than the address for a range. “A1” is a range string that points to cell A1. “A1:G5” is also a range string; it points to a range of cells encompassing cells A1 to G5. In Step 2, you're building a range string that encompasses the double-clicked cell (called Target in this macro), the entire active row, and the entire active column. The Address property for these three ranges is captured and pieced together into the strRange variable.

In Step 3, you feed the strRange variable as the address for a Range.Select statement. This line of the code finally highlights the double-clicked selection.

How to use the macro

To implement this macro, you need to copy and paste it into the Worksheet_BeforeDoubleClick event code window. Placing the macro there allows it to run each time you double-click on the sheet.

1. Activate Visual Basic Editor by pressing Alt+F11.
2. In the project window, find your project/workbook name and click the plus sign next to it to see all the sheets.
3. Click the sheet from which you want to trigger the code.
4. In the Event drop-down list, select the BeforeDoubleClick event (see Figure 5-10).
5. Type or paste the code in the newly created module.