
Home	Excel	Access
\| Feedback \| Keyboard shortcuts \|

Moving between Excel workbooks - a macro and toolbar button

This page illustrates a simple macro that can be placed in your Personal.xls workbook and is then available to switch between all open workbooks in turn. This is an alternative to selecting the Window menu option (or using a keyboard shortcut) and then choosing the required workbook.

Sub NextBk()
Dim NumBooks As Integer, n As Integer
Dim ThisBook As Integer, FirstBk As Integer

NumBooks = Workbooks.Count
If Workbooks(1).Name = "PERSONAL.XLS" Then
FirstBk = 2
Else
FirstBk = 1
End If

For n = FirstBk To NumBooks
If Workbooks(n).Name = ActiveWorkbook.Name Then ThisBook = n
Next

If ThisBook < NumBooks Then
' move to the next books
Workbooks(ThisBook + 1).Activate
End If

If ThisBook = NumBooks And NumBooks > FirstBk Then
' move back to first book
Workbooks(FirstBk).Activate
End If

End Sub

Excel allows you to customise your toolbar and add buttons for useful procedures and options. A macro can be linked to a new button and then becomes available whenever Excel is opened.

Step 1 Open the VBA editor (Tools | Macros | Visual Basic Editor). Select the Personal.xls VBA Project and Insert | Module. Copy the code from this page into the module to create a new procedure called NextBk().

Step 2 Close the VBA editor and return to Excel. Right button click with the mouse on a toolbar and select Customise. On the Commands table select the Macros category. Then drag the smiley face custom button onto your toolbar.

Step 3 Right click on your new button to open the short menu and change it's properties. Add a name (which will appear as a pop up hint when your cursor hovers over the button) and assign the NextBk() macro to it. You can also edit the button image and design your own masterpiece.

Would you like to learn more?

Step by Step Excel 2003 (with CD)

Four keyboard short cuts

Use <ctrl> + apostrophe (') to copy or duplicate the contents from the cell directly above. This also works in Access tables.
Use <alt> + equals (=) to apply the AutoSum function. This places the Sum() function in the cell and Excel makes it's best guess as to the range of cells you wish to add up.
When inputting a function use <ctrl> + <shift> + 'A' to reveal prompts for each of the required arguments. This applies when you are typing a function directly into the formula bar. For example if you type =MATCH followed by <ctrl> + <shift> + 'A', the following prompt appears: =MATCH(lookup_value, lookup_array, match_type). Edit the details, replacing each prompt word with the appropriate value.
To switch between open workbooks press <ctrl> + <tab>.

Get this information as a document
accompanied by Excel worksheets

Click here for details about
obtaining this file

file: xlnextwindow.htm

Last updated Sep06