Excel Zoom

Adding comments to your worksheet allows you to share your thoughts with other users, or to serve as a reminder for yourself.

Right click the cell where you want to insert the comment, and begin typing in the box that appears. Click off the cell and you’ll see a little red triangle in the upper right hand corner of the cell where you just inserted a comment. This is a visual cue to let a user know that there is a comment in that cell.

To view that comment, simply hover over the cell, or right click the cell and select Show/Hide Comments. Hide the comment by right clicking the cell again and selecting Hide Comment.

To edit a comment, right click and select Edit Comment.

To delete a comment, right click and select Delete Comment.

Excel 2007 allows you to do all the previously mentioned actions on the Review tab under the Comments group.

There are several different types of charts found in Excel. All of which are used to display information in a way that makes it easier to interpret data.

• Column charts are best suited to compare distinct object levels in a vertical format.
• Line charts compare trends over even time periods, but are not necessarily used to show totals.
• Pie charts show the relative size of a particular data point as a part of the whole.
• Bar charts are best for comparing distinct object levels ina horizontal format. It is similar to a column chart, just flipped on its side.
• Area charts show how individual volume changes over time in relation to a toal volume.
• Scatter charts compare trends over uneven time or measurement intervals. This type of chart is useful to spot trends in a set of data.

Often times, a project manager will be given a project’s start date and the number of days it should take to complete. Considering, however, that most people work Monday through Friday, you can’t simply add a certain number of days to the starting day’s date to come up with your ending date, because that wouldn’t take into consideration weekends and holdiays.

Using the formula =WORKDAY() allows you to specify a beginning date, the number of work days until the ending date, and any holidays that might fall between the start and finish of the project.

If you wish to specify holidays, enter the holiday’s date value in the formula (i.e. Memorial Day in 2009 is May 25, 2009, or in Excel’s number format, 39958). This can be determined by using Excel’s =DATE() formula.

More on these formulas can be found on our Wiki page.

• WORKDAY Wiki Page
• DATE Wiki Page
• NETWORKDAYS Wiki Page

Have you ever had a spreadsheet, where you wanted users to be able to enter information in a form, and have it populate the next available row in a given set of columns? It’s easier than you thought, and will make entering data more efficient and accurate.

The example below will use most of the common UserForm tools to give you a feel for how they work. You can later apply this example to your own custom forms.

This example will create a form to ask users to input information about your customers, and upon successfully filling out the form will populate a spreadsheet with the appropriate information. Note: There’s a full working example of this UserForm available to download by clicking here.

The first step is to set up your spreadsheet to accept the data you’ll ask your users to populate. (Note: don’t type the quotes shown below).

• Open a new workbook in Microsoft Excel. In “Sheet1″ change the sheet’s name to “Customer Data”.
• In cell A1, type “Business Name”.
• In cell B1, type “Customer Contact Name”.
• In cell C1, type “Street Address”.
• In cell D1, type “City”.
• In cell E1, type “State”.
• In cell F1, type “Zip”.
• In cell G1, type “Phone”.
• In cell H1, type “Fax”.
• In cell I1, type “Email”.
• In cell J1, type “Active”.
• In cell K1, type “Past Due”.

The second step is to create your UserForm and add the controls that users will ultimately populate with data.

• Open the Visual Basic Editor (Alt+F11).
• While in the Visual Basic Editor, insert a new UserForm (Insert | UserForm). Also make sure the Toolbox menu appears when you do this. If not, click View | Toolbox.
• To insert a control, select it from the Toolbox and drag it to where you want it in the form. Note: you can drag the edges of the UserForm and the controls to make them bigger/smaller, depending on how much data you’ll be entering.

You’ll want to enter the following controls below into your UserForm.

• 8 TextBoxes. 1 for each of the following: Business Name; Customer Contact Name; Street Address; City; Zip; Phone; Fax; Email. Note: You may also want to include a Label for each textbox to visually tell the user what information should be entered into each box.
• 1 ComboBox for the State field.
• 1 CheckBox. This is to check if the customer is an active customer.
• 1 Frame. Within this frame put five OptionButtons. This is where you’ll indicate if they’re past due, or not and if so, how long past due. This will populate column K above.
• Finally, 3 CommandButtons. One that will say “OK”, another that will say “Cancel” and a third that will say “Reset Form”.

Use the following configuration settings for each of the controls in your form.

Now that you’ve created your form in Visual Basic, you need to initialize it.

• View the form’s code, by clicking View | Code or F7.
• You’ll use the names of each form control in this process, so make sure you know what each is.
• Copy and paste the following code into your form’s code.


Private Sub UserForm_Initialize()

txtBName.Value = ""
txtCustContact.Value = ""
txtZip.Value = ""
txtStreet.Value = ""
txtPhone.Value = ""
txtFax.Value = ""
txtEmail.Value = ""
txtCity.Value = ""
With cboState

txtBName.SetFocus
End Sub

• This will clear any old data out of your UserForm and get it set for editing, by setting default values. If, for example the majority of your customers are from Texas, you could change the value for cboState.Value = “” to cboState.Value = “TX”. This saves the end user one additional step in the process. Also, for purposes of this example, we’ll assume that the majority of our customers are not past due with payments, so we’ll set opNo = True to set the Past Due option button of Not Overdue to be the default option.

Now, go back to the UserForm, by clicking frmCustInfo in the left pane. Now double click on your Cancel button. This brings you back to the code editor. You’ll see the following code:
Private Sub cmdCancel_Click()

End Sub

Enter the following in between those two lines: Unload Me
You code should look like this:
Private Sub cmdCancel_Click()

Unload Me

End Sub

Now do the same for the Reset Form button. This time, in between Private Sub cmdReset_Click() and End Sub, enter Call UserForm_Initialize, so your code should look like this:
Private Sub cmdReset_Click()

Call UserForm_Initialize

End Sub

Next, we’ll code the OK button, so we’ll follow the same process, but this time the code that will go in between Private Sub cmdOK_Click() and End Sub is:
ActiveWorkbook.Sheets(“Customer Data”).Activate
Range(“A2″).Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtBName.Value
ActiveCell.Offset(0, 1) = txtCustContact.Value
ActiveCell.Offset(0, 2) = txtStreet.Value
ActiveCell.Offset(0, 3) = txtCity.Value
ActiveCell.Offset(0, 4) = cboState.Value
ActiveCell.Offset(0, 5) = txtZip.Value
ActiveCell.Offset(0, 6) = txtPhone.Value
ActiveCell.Offset(0, 7) = txtFax.Value
ActiveCell.Offset(0, 8) = txtEmail.Value
If chkActive = True Then
ActiveCell.Offset(0, 9).Value = “Yes”
Else
ActiveCell.Offset(0, 9).Value = “No”
End If
If opL90 = True Then
ActiveCell.Offset(0, 10).Value = “Less Than 90 Days”
ElseIf op90 = True Then
ActiveCell.Offset(0, 10).Value = “Over 90 Days”
ElseIf op180 = True Then
ActiveCell.Offset(0, 10).Value = “Over 180 Days”
ElseIf op360 = True Then
ActiveCell.Offset(0, 10).Value = “Over 360 Days”
Else
ActiveCell.Offset(0, 10).Value = “Not Overdue”
End If
Range(“A1″).Select
End Sub

Since we set the past due options “Enabled” property to False, we need to be able to tell the form when to set it to True. We’ll assume that we only want the past due options to be editable if the customer’s account is active. We can do so with the following code:
Private Sub chkActive_Change()
If chkActive = True Then
frPastDue.Enabled = True
opNo.Enabled = True
opL90.Enabled = True
op90.Enabled = True
op180.Enabled = True
op360.Enabled = True
Else
frPastDue.Enabled = False
opNo.Enabled = False
opL90.Enabled = False
op90.Enabled = False
op180.Enabled = False
op360.Enabled = False
End If
End Sub

Finally, we need to be able to let users bring up this form from within Excel. We can do this with a simple macro that looks like this:
Sub OpenCustomerInfoForm()
frmCustInfo.Show
End Sub
This macro needs to be placed either in a module, or in a Microsoft Excel Object (This Workbook, Sheet1, Sheet2, etc.). It should not be placed in with the rest of your form’s code, as you won’t be able to run the code from Excel.

Download a full working example of the customer contact user form we just created.

Sometimes we setup Excel worksheets so that they flow logically on the screen. Often times they’re set up like you would read a book, top to bottom, left to right, but this isn’t always the best setup for printing.

Suppose you have a spreadsheet with detailed information in the first 50 rows, and then a summary in the next 10. There could be times where you don’t care to print the details, and only want the summary, or the opposite. Of course, you could set up your spreadsheet so that the details are in one worksheet and the summary is in another, each with their print areas set up correctly. But that would make viewing the spreadsheet on the screen that much more difficult, as you would have to click between the two in order to analyze the data.

This is where custom views help. Read below for a step-by-step example on how to setup custom views.

This example assumes that you have your detailed information (print area # 1) in the first 50 rows in columns A through M, and your summary information (print area # 2) in the next 10 rows in the same columns. You can set more than just two print areas using custom views, but for simplicity’s sake, we’ll use two for now.

• First, select print area # 1 (cells A1:M50)
• Click File | Print Area | Set Print Area
• Click File | Page Setup and set your print settings for that area (i.e. header/footer, portrait/landscape, letter/legal/other paper size, fit page to 1 by 1 or 100%, etc.)
• Click View | Custom Views and then click the “Add” button. In the dialog box, enter a description of the view (in this case, “print_detail” without the quotes would work)
• Save your file.

Now that you have your first print area setup, we’ll repeat the previous steps to setup the second print area for the summary section.

• Select print area # 2 (cells A51:M60)
• Click File | Print Area | Set Print Area
• Click File | Page Setup and set your print settings for that area (i.e. header/footer, portrait/landscape, letter/legal/other paper size, fit page to 1 by 1 or 100%, etc.)
• Click View | Custom Views and then click the “Add” button. In the dialog box, enter a description of the view (in this case, “print_summary” without the quotes would work)
• Save your file.

OK, both print areas are setup. Now we need to be able to use them.

• Click View | Custom Views
• You should see the two custom views we just created in the box, print_details and print_summary
• Click on the one you wish to print and then click the “Show” button
• You can double check that the print area is set to your liking by clicking File | Print Preview. This will show how your printout will look after printing. If you don’t need to make any changes, simply click File | Print.

Added Bonus – Set a Macro to Print Your Custom View

So far we’ve covered how to set a custom view to make printing easier. We’ll lets go one step further and make it even easier.

Insert the following macro code in your Visual Basic Editor to have Excel select the custom view and print it out all in one click!

Note: The macro code will print one custom view (currently set to print the details from the example above). You’ll need to copy this macro for as many custom views as you have. For each custom view change print_custom_view() to a unique name and also change the line “print_details” to whatever you have named your custom view.

Sub print_custom_view()

End Sub

Below, you’ll find a fairly comprehensive list of default shortcut keys found on a standard English keyboard in Excel.

It’s always useful to have a calendar in Excel. Having one lets you save it on a shared network drive, so everyone involved in a particular project knows what’s going on and important dates coming up. Creating a calendar in Excel can be a tedious task if done manually. Thankfully, using the Visual Basic Editor, you can easily create a calendar in Excel.

By creating a simple macro, you can have Excel ask a user to input the month and year, and let Excel do the rest!

I’m assuming that if you’re reading this you’ve already taken a look at my guide that describes how to install and use macros. If not, read up on the Single Workbook Use section for purposes of this macro.

I’ll explain each step of this macro code, and then put it all together at the end, to help you understand what it’s doing.

The first part of the code is to make sure the current sheet is unprotected. This will enable the macro to make the necessary edits to the sheet. This is accomplished with the following line:
ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _
Scenarios:=False
Application.ScreenUpdating = False

Next, you’ll want to make sure that any errors from data entry are resolved so that the macro can work properly. (More on this later).
On Error GoTo ErrorMessage

Here, we’ll tell the macro to clear whatever is in the cells where we want the calendar to appear.
Range(“A1:G14″).Clear

In order to get users to input a date, you’ll need to tell Excel to open an input box and ask the user to input the date. You’ll also want the date the user puts into the box to be defined as a variable. If they don’t input anything, or cancel out of the input box, you’ll want to end the process.
InputDate = InputBox(“Type in Month and Year for Calendar “)
If InputDate = “” Then Exit Sub

In order for the macro to properly format the calendar in Excel, it needs to know what the first day of the month is. Since we just had our users input the month and year, we need to convert this into a number that Excel recognizes as a date. You’ll also want to make sure that the date it is formatting is the first day in the month, otherwise tell it to change to the first day.
FirstDay = DateValue(InputDate)
If Day(FirstDay) <> 1 Then
FirstDay = DateValue(Month(FirstDay) & “/1/” & _
Year(FirstDay))
End If

Next, you’ll want to start formatting your cells so it starts to look like a calendar.
Range(“A1″).NumberFormat = “mmmm yyyy” — This formats the header to display just the month and year.
Here, we set up the cells that will contain the days of the week
With Range(“A1:G1″)
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
.Font.Size = 18
.Font.Bold = True
.RowHeight = 35
End With
With Range(“A2:G2″)
.ColumnWidth = 14
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = xlHorizontal
.Font.Size = 12
.Font.Bold = True
.RowHeight = 20
End With
Range(“A2″) = “Sunday”
Range(“B2″) = “Monday”
Range(“C2″) = “Tuesday”
Range(“D2″) = “Wednesday”
Range(“E2″) = “Thursday”
Range(“F2″) = “Friday”
Range(“G2″) = “Saturday”

Now we want to input the dates, and most importantly we want to put them in the correct location.
With Range(“A3:G8″)
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
.Font.Size = 18
.Font.Bold = True
.RowHeight = 21
End With
Range(“A1″).Value = Application.Text(InputDate, “mmmm yyyy”)
DayofWeek = Weekday(StartDay)
CurrenttYear = Year(StartDay)
CurrentMonth = Month(StartDay)
LastDay = DateSerial(CurrentYear, CurrentMonth + 1, 1)
Select Case DayofWeek
Case 1
Range(“A3″).Value = 1
Case 2
Range(“B3″).Value = 1
Case 3
Range(“C3″).Value = 1
Case 4
Range(“D3″).Value = 1
Case 5
Range(“E3″).Value = 1
Case 6
Range(“F3″).Value = 1
Case 7
Range(“G3″).Value = 1
End Select

OK, now we have the first day of the month in, now we need to get the rest in, but considering that months all have different numbers of days, and some months (February) can have a different number of days from year to year, we need to tell the macro to only put as many days as there are in the month.
For Each cell In Range(“A3:G8″)
RowCell = cell.Row
ColCell = cell.Column
If cell.Column = 1 And cell.Row = 3 Then
ElseIf cell.Column <> 1 Then
If cell.Offset(0, -1).Value >= 1 Then
cell.Value = cell.Offset(0, -1).Value + 1
If cell.Value > (LastDay – FirstDay) Then
cell.Value = “”
Exit For
End If
End If
ElseIf cell.Row > 3 And cell.Column = 1 Then
cell.Value = cell.Offset(-1, 6).Value + 1
If cell.Value > (LastDay – FirstDay) Then
cell.Value = “”
Exit For
End If
End If
Next

Now that we have the dates, we’ll want to create a little space to enter in some data. To do this we’ll create a new row under each row that contains the dates.
For x = 0 To 5
Range(“A4″).Offset(x * 2, 0).EntireRow.Insert
With Range(“A4:G4″).Offset(x * 2, 0)
.RowHeight = 65
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.Font.Size = 10
.Font.Bold = False
.Locked = False
End With

Now we’ll format the calendar and the rest of the sheet so it actually looks like a calendar.
With Range(“A3″).Offset(x * 2, 0).Resize(2, 7).Borders(xlLeft)
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Range(“A3″).Offset(x * 2, 0).Resize(2, 7).Borders(xlRight)
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Range(“A3″).Offset(x * 2, 0).Resize(2, 7).BorderAround Weight:=xlThick, ColorIndex:=xlAutomatic
Next
If Range(“A13″).Value = “” Then Range(“A13″).Offset(0, 0) .Resize(2, 8).EntireRow.Delete
ActiveWindow.DisplayGridlines = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWindow.WindowState = xlMaximized
ActiveWindow.ScrollRow = 1

Now we’ll end the macro, assuming there were no errors encountered along the way.
Application.ScreenUpdating = True
Exit Sub

If there were errors, we’ll need to define the error message from above.
ErrorCorrect:
MsgBox “You may not have entered your Month and Year in the correct format.” _
& Chr(13) & “Spell the Month correctly” _
& ” (or use 3 letter abbreviation)” _
& Chr(13) & “and 4 digits for the Year”
InputDate = InputBox(“Type in Month and Year for Calendar”)
If InputDate = “” Then Exit Sub
Resume
End Sub

Now that you’ve seen the pieces, here’s the complete code.

Sub Create_Calendar()
'
'MACROS BY EXCELZOOM.COM
'
ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _
Scenarios:=False

Application.ScreenUpdating = False

On Error GoTo ErrorMessage

ErrorMessage:
MsgBox "You may not have entered your Month and Year correctly." _
& Chr(13) & "Spell the Month correctly" _
& " (or use 3 letter abbreviation)" _
& Chr(13) & "and 4 digits for the Year"
InputDate = InputBox("Type in Month and Year for Calendar")
If InputDate = "" Then Exit Sub
Resume
End Sub


So you’ve found the perfect macro code to speed up that tedious and repetitive task – now what? In this section, we’ll walk you through the several ways to install and use your macro code.

First, MAKE A BACKUP COPY OF YOUR FILE! There are some macros that can’t easily be undone once they are run. It is always important to have a back-up copy of your work incase something goes unexpectedly wrong.

Record a Macro:

The first, and easiest way is to let Excel do the work for you. In earlier versions of Excel (pre Excel 2007), click ‘Tools’ -> ‘Macro’ -> ‘Record New Macro’. In Excel 2007, click the ‘Developer’ menu then ‘Record New Macro’. Enter in the appropriate information in the Record Macro dialog box (macro name, shortcut key (if desired), location to store the macro, and description (if desired)), then click ‘OK’. Excel will record every command and keystroke you make until you click the ‘Stop Recording’ button. There’s no need to enter any additional information, Excel has everything it needs to run your macro!

If you found the perfect macro to use on Excel Zoom’s macro list, you’ll need to “install” it before using it. There are several ways to do this and which one you choose depends on how you are planning to use the macro.

Single Workbook Use:

If you only want the macro to run in one particular workbook, and don’t want it available everytime you open Excel, this is where you want to be.

• Open up the workbook you wish to use the macro in. This can be a new or existing workbook, it doesn’t matter. For illustrative purposes these instructions will assume you’re using a new workbook, called “Book1″
• In pre-Excel 2007, click ‘Tools’ -> ‘Macro’ -> ‘Visual Basic Editor’ or simply press ‘Alt+F11′. In Excel 2007, click the ‘Developer’ menu then ‘Visual Basic’.
• A new window should have popped up that looks nothing like Excel. Don’t be alarmed this is what we want. In the left hand pane you should see a tree that says something along the lines of “VBAProject (YourExcelFileName)”, obviously replacing YourExcelFileName with, well, your Excel file’s name. Mine says “VBAProject (Book1)”. Under that there should be a folder, called “Microsoft Excel Objects” that lists all the worksheets in your file.
• Right click on ‘VBAProject (YourExcelFileName)’ -> ‘Insert’ -> ‘Module’. This should create a second folder under “VBAProject (YourExcelFileName)”, called “Modules”. You’ll also notice a blank screen over in the right hand pane. This is where we’ll be pasting in your macro code. Note: If you already have a second folder called “Modules”, you can skip this step, and just double click on “Module1″ in the folder.
• Now, go back to the wonderful time saving macro you found on ExcelZoom.com, click in the text area, which automatically selects all the code for you (cool isn’t it), and copy it (Ctrl+C, or right click and Copy).
• Go back to your Visual Basic Editor (that thing you opened that doesn’t look like Excel). Right click in the blank right hand pane and paste in your code. It’s ok if you already have macros there and this pane isn’t blank, simply scroll down to the bottom and paste the code after your last macro.
• Now, close the Visual Basic window, you’re done there. Go back to your workbook and click ‘Tools’ -> ‘Macro’ -> ‘Macros’ in pre-Excel 2007, or simply press ‘Alt+F8′. In Excel 2007, go to the ‘Developer’ menu and click ‘Macros’.
• A list of all available macros should appear, including your recently installed macro. Select the macro in the menu you wish to run and click the ‘Run’ button. Then sit back, relax, go get a cup of coffee, take a nap, whatever you think you’ve earned with all that extra time you have!
• It is also important to note that there are other ways to run your macros, which are covered below.

Make a Macro Available to All Workbooks Using PERSONAL.XLS:

Suppose you have a macro that you want to be able to use in any Excel file you open. No problem! Follow the steps below, and you’ll have access to your macros in any Excel file you open.

• First, you’ll need to make sure that you have a Personal Macro workbook. It should be stored in the folder that Excel uses to open all files at startup in. To find which folder this is, click ‘Tools’ -> ‘Options’ -> ‘General’ tab, and find the file location labeled “At startup, open all files in:”. Mine is set to “C:Program FilesMicrosoft OfficeOffice10xlstart”, yours should be something similar, unless you’ve changed this. In any case, click ‘Cancel’ and go to the folder specified in that box.
• If there is a file in that folder called “PERSONAL.XLS”, open it, otherwise, right click and select ‘New’ -> ‘Microsoft Excel Worksheet’. Call it PERSONAL.XLS and open it.
• From here, the steps are the same as if you were planning on using the macro in a single workbook (above).
• In pre-Excel 2007, click ‘Tools’ -> ‘Macro’ -> ‘Visual Basic Editor’ or simply press ‘Alt+F11′. In Excel 2007, click the ‘Developer’ menu then ‘Visual Basic’.
• A new window should have popped up that looks nothing like Excel. Don’t be alarmed this is what we want. In the left hand pane you should see a tree that says something along the lines of “VBAProject (YourExcelFileName)”, obviously replacing YourExcelFileName with, well, your Excel file’s name. Mine says “VBAProject (PERSONAL.XLS)”. Under that there should be a folder, called “Microsoft Excel Objects” that lists all the worksheets in your file.
• Right click on ‘VBAProject (YourExcelFileName)’ -> ‘Insert’ -> ‘Module’. This should create a second folder under “VBAProject (YourExcelFileName)”, called “Modules”. You’ll also notice a blank screen over in the right hand pane. This is where we’ll be pasting in your macro code. Note: If you already have a second folder called “Modules”, you can skip this step, and just double click on “Module1″ in the folder.
• Now, go back to the wonderful time saving macro you found on ExcelZoom.com, click in the text area, which automatically selects all the code for you (cool isn’t it), and copy it (Ctrl+C, or right click and Copy).
• Go back to your Visual Basic Editor (that thing you opened that doesn’t look like Excel). Right click in the blank right hand pane and paste in your code. It’s ok if you already have macros there and this pane isn’t blank, simply scroll down to the bottom and paste the code after your last macro.
• Now, close the Visual Basic window, you’re done there. Go back to the file called “PERSONAL.XLS”. This is where it gets a little different from the steps for a single file use above.
• Click ‘Window’ -> ‘Hide’. Considering that this file will open everytime Excel opens, you want it to open in the background, where you won’t see it. If you ever wish to add/edit/delete a macro in PERSONAL.XLS, just click ‘Window’ -> ‘Unhide’ and select PERSONAL.XLS from the list.
• Close Excel. If it prompts you to save PERSONAL.XLS, click yes to save it.
• Open a new or existing Excel file. You should only see that file open up, not PERSONAL.XLS as it was set to open in the background.
• Click ‘Tools’ -> ‘Macro’ -> ‘Macros’, or press ‘Alt-F8′. You should see a list of macros that were entered into PERSONAL.XLS. They will be “prefixed” by “PERSONAL.XLS!” then the macro name. They’re ready to use and be run in any workbook you open.
• See below for ways to run your macros.

 Running your Macros

There are three ways to run your macros. 1) Using the ‘Tools’ menu; 2) Creating a toolbar button; 3) Assigning a keyboard shortcut to your macro.
The following assumes that you already have macros available to use.

1) Using the ‘Tools’ Menu:

• Select the cell (or cells) you wish your macro to modify, if necessary.
• Click ‘Tools’ -> ‘Macro’ -> ‘Macros’ in pre-Excel 2007, or simply press ‘Alt+F8′. In Excel 2007, go to the ‘Developer’ menu and click ‘Macros’.
• Select the macro you wish to run.
• Click ‘Run’.

2) Creating a Toolbar Button:

• Click ‘Tools’ -> ‘Customize’ -> ‘Commands’ tab.
• In the ‘Categories’ menu, select ‘Macros’.
• Select ‘Custom Button’ and drag it to the toolbar position where you want it.
• In the ‘Commands’ tab, select ‘Modify Selection’ -> ‘Assign Macro’.
• Select the macro you wish to assign to that button and click ‘OK’.
• You can modify the button, by selecting ‘Modify Selection’ again, click ‘Change Button Image’ and select a different image.
• If you wish to add text to the button, select ‘Modify Selection’, change the description in the ‘Name’ box, and select either ‘Image and Text’ or ‘Text Only’ depending on which you prefer.
• Play around with some of the other options to get the custom button exactly the way you want it.

3) Assigning a Keyboard Shortcut to Your Macro:

• Click ‘Tools’ -> ‘Macro’ -> ‘Macros’ in pre-Excel 2007, or simply press ‘Alt+F8′. In Excel 2007, go to the ‘Developer’ menu and click ‘Macros’.
• Select the macro you wish to assign a shortcut to and then click ‘Options’.
• Type the key you wish to use with CTRL in order to run your macro.
• IMPORTANT: Do not use a key that is already associated with other Microsoft functions (i.e. CTRL+A already is a shortcut to select all).