Create a Word Keyboard Shortcuts List

If you like to use keyboard shortcuts, you might have some of them memorized, in Word and Excel, or other programs.

If your memory isn’t too good, you can print a list of shortcuts and keep it near your computer as a reference.

A few months ago, I mentioned some sites where you can download lists of Excel keyboard shortcuts. It’s even easier to get a list of Word keyboard shortcuts. There’s a built in macro that you can run, to create the list.

Create a List of Word Keyboard Shortcuts

  • In Word 2003, click the Tools menu, click Macro, then click Macros.
  • In the Macros dialog box, click the dropdown arrow for ‘Macros in’ and select Word commands
  • In the list of macros, click on ListCommands, then click Run.

WordListCmds

  • In the List Commands dialog box, click on Current menu and keyboard settings, then click OK

A new document will be created, with a table that lists all the keyboard shortcuts and menu commands.

WordCmdList

______________________

Use a Web Graphic As Desktop Background

Here’s another Windows tip that I found while cleaning my office. If you find a picture while surfing the Internet, you can quickly add that photo to your Windows desktop as a background.

The desktop photo is usually covered with documents, but it’s nice to see a happy face or calming scenery when you close all your files.

Add a Graphic to the Windows Desktop

  1. Right-click on the graphic that you want to add to the desktop.
  2. Click on Set as Desktop Background
  3. From the Position drop down, select an option. (Center is my preference.)
  4. Click Set Desktop Background

WindowsBackground

__________________

Create Single Click Desktop Icons

One of my holiday projects was to clean out some old client files, and the five bags of shredded paper are evidence of my success. In one of the files, I found notes from Windows 98 training that I did for a client.

Obviously, a few things have changed, but most of the tips still work. I had forgotten that the desktop icons could be changed to work on a single click, instead of a double click. Here are the instructions, modified for Windows XP.

Create Single Click Icons

Note: This will also affect the files in your Window Explorer folders.

  1. Double-click the My Computer icon
  2. Click the Tools menu, then click Folder Options
  3. Click the General tab
  4. Under ‘Click items as follows’, click ‘Single-click to open an item (point to select)
  5. Select the formatting option that you prefer (I don’t like underlining, so I selected ‘Underline icon titles only when I point at them’)
  6. Click OK

FolderSingleClick

______________________

Open a Second Window in Outlook 2007

Occasionally it’s useful to see two main windows in Outlook 2007 at the same time. For example, if you’re clearing out your Inbox, you might want to check your Contact list, to see if someone’s name is already in there.

Instead of flipping back and forth between views, you can open a second window.

Show the Navigation Pane

If the Navigation Pane isn’t visible, click the View menu, then click Navigation Pane and click Normal.

OutlookNavPane

To open a second window:

  1. In the Navigation Pane, right-click on a button, such as Contacts.
    Note: If the Navigation Pane is minimized, or the buttons are displayed as icons at the bottom of the Navigation Pane, you can right-click on those.
  2. Click on Open in New Window

OutlookNewWindow

________________________

Have an Excellent Christmas!

Merry Christmas! Posting will be very light over the next week and a half (just the Excel Twitter posts), and will be back to normal on January 5th.

Thank You!

Thanks for reading the Contextures Blog and I look forward to sharing tips with you in 2009, and getting your much appreciated feedback.

More Christmas Trees

There are more Excel Christmas trees on my Contextures site, so go and take a look at those ones too!

This short video shows how to build an Excel Christmas tree. There’s a scroll bar on the worksheet, and you use that to add decoration, one layer at a time.

This technique is done with NO macros!

Get Christmas Excel Files

There are lots more holiday workbooks on my Contextures site, on the following pages:

Excel Advent Calendars

Excel for the Holidays

Excel Christmas Planner

Open a New Tab in Firefox

Well, it’s Christmas Eve, and you’re probably too busy to read a long blog post today. So, here’s a quick tip that I discovered this week.

I use Firefox as my browser, and when I need a new tab, I right-click in the tab area, then click on New Tab.

ClickNewTab

Now, I realize that isn’t a terrible hardship, but it’s much easier to create a new tab in other browsers, that shall remain nameless.

I know there’s a New Tab button, and I even added that to my toolbar, but never remember to use it.

NewTabButton

Double-Click For a New Tab

This week, I accidentally double-clicked in the tab area, to the right of the last tab (or in the sliver of space above the tabs, if your aim is good), and presto! A new tab was instantly created.

It’s still two clicks, but for some reason I find this quicker and easier. It’s the small things. 😉

Happy Holidays!
_______________

Excel Worksheet Event-Show Message Example

On my website there are sample Excel files, many of which use VBA event programming to make things happen.

What is an Event?

For Excel VBA program, an event procedure is code that runs automatically, after an “event” occurs.

An event is something that happens in an Excel workbook or worksheet. For example:

  • Type something in a cell (worksheet change event)
  • Click on a cell to select it (worksheet selection change event)
  • Click on a worksheet tab to select it (worksheet activate event)

In the Excel Visual Basic Editor, if you’re working on a Worksheet module, you can see a list of the worksheet events.

Excel VBA Worksheet Events
Excel VBA Worksheet Events

Worksheet Event Code

With event programming, it’s easy to automate your worksheets and workbooks. People don’t have to remember to click a button, or run a macro from the Macro list.

Instead, your code runs automatically when a specific event occurs. People just need to change a cell, or refresh a pivot table, and the code for that event will run.

Event Programming Example

For example, on this worksheet there’s a data validation drop down list in cell B2, where a day of the week can be selected.

data validation drop down list with weekdays
data validation drop down list with weekdays

Select a Weekday

When you select a weekday, that changes the worksheet.

If the worksheet has VBA code for the Worksheet_Change event, it will run automatically, if any cell on the worksheet is changed

In this example, the Worksheet_Change event code checks the address of the cell that was changed, to see if it was cell B2.

  • If it was cell B2, a message is displayed — “Please update the daily roster.”
  • If any other cell was changed, nothing happens

EventMessage

View the Worksheet Code

To see the code for a worksheet, right-click on its sheet tab, and click View Code.

ViewCode

The Visual Basic Editor opens, and shows the code for the active sheet.

ViewCodeMsg

Multiple Worksheet Events

What happens if you try to create multiple Worksheet_Change events on the same worksheet?

For example that message code for cell B2 works well, so you’d like to add a similar Worksheet_Change event, for a different cell.

  • If you make a change in cell B4, you’d like the date automatically entered in cell B5.

Visual Basic Error Message

Unfortunately, if you add another Worksheet_Change event, you’ll see an error message when you change a cell.

In the screen shot below, the VBA error message says:

  • Compile error: Ambiguous name detected: Worksheet_Change

That message appears if you use the same procedure name twice on the worksheet.

Compile error: Ambiguous name detected: Worksheet_Change
Compile error: Ambiguous name detected: Worksheet_Change

Combine the Event Code

Instead of creating separate events with the same name, combine both pieces of code into one.

For example, you could use Select Case, and specify what should happen when specific cells are changed.


Some situations will require a more complex solution, and if you experiment a bit, you should be able to include multiple outcomes within a single worksheet event’s code.
______________________

Automatic Backup for Access Files

Most of my blog posts are about Microsoft Excel, but I use Microsoft Access every day too! I’ve got two big database for my own work, and I help several clients with their Access databases.

Save Your Access Files

If you use Access too, do you have Access databases open throughout the workday, with people entering and editing data?

A helpful feature in Access is that data is saved automatically as you work, as soon as you exit a record.

However, it’s a good precaution to have a backup of the entire file, so you can recover if something goes wrong.

Access Backup Tool

To make backups easy to manage, Jan Karel Pieterse has created the Access Backup Tool, available as a free download on his web site.

I tested it in Access 2007 and Access 2003, and it worked well in both version.

Getting Started

Open the Access Backup Tool and the Menu form will appear automatically.

Access Backup Tool Dialog Box
Access Backup Tool Dialog Box

Access Backup Tool Settings

Next, click the Settings button in the Access Backup Tool Dialog Box.

That opens the Settings form, where you can enter the information about your databases.

Access Backup Tool Settings
Access Backup Tool Settings

In the Settings form, enter the file path to each database, and the location where you want the backup stored.

Timed or Manual Backups

At the top of the Settings form, you can enter the backup settings.

  • For automatic backups, add a check mark to Timed Backup, and set the time interval for the backups. Then the backup process will run automatically, at the specified interval.
  • For manual backups, remove the check mark for Timed Backups.

Suspend Backups Overnight

If backups aren’t required overnight, you can suspend them during specified hours.

  • To stop backups overnight, add a check mark to Suspend backup. Enter the stop and start times for the backups.
  • To run the backups around the clock, remove the check mark from Suspend backup

Start the Backups

After you’ve entered all the settings, close the Settings form and return to the Menu.

Timed Backups

On the Menu form, if you opted for Timed Backup, you’ll see the countdown to the next backup. At the specified interval, the backup will run, and you’ll see a notice on the menu.

Leave the Access Backup Tool running all day, and it will take care of the backups for you automatically.

ABU_Run2003

Manual Backups

If you’d prefer to run the backups manually, click the Backup Databases button, and all the selected databases will be backed up.

_____________

Excel Financial Modeling Book

I’m the technical reviewer for Pro Excel Financial Modeling: Building Models for Technology Startups, by Tom Sawyer, which will be published in April 2009.

Here’s how Tom describes the book:

“This is a great opportunity for me to summarize and share fifteen years of experience helping startup and early stage companies move from concept to operational status. The book combines important business concepts and practices with practical ‘how to’ information on financial modeling and analysis.”

Apress Alpha Book Program

Some chapters of the book are already available for purchase through the Alpha Books program at Apress.

In the Alpha Books program, you get immediate access to chapters of the book, as they’re being written and reviewed. You can provide feedback to the author, and help improve the final product.

Get 4 Chapters

There are four chapters available for download now, and more chapters will be added to the download as work progresses.

If you buy the Alpha Book, you’ll be notified when new material is ready.

Then, go back to the Apress site and download it.
________________

Pro Excel Financial Modeling book cover
Pro Excel Financial Modeling book cover

Is Your Excel VBA Code Clean?

Do you write Excel macro, with VBA programming? Is your code clean?

Clean Code Wristband

While I was browsing the Internet recently, I saw someone promoting a “Clean Code” wristband, with donations going to a non-profit group.

Writing clean code is an idea worth supporting, and you could snap that wristband, to wake yourself up, if your coding starts to go off track.

Green Band

Excel VBA Tutorials

If you’d like to get started with Excel VBA programming, there are several pages on my Contextures site, where you can find tutorials, sample files, and videos.

1 — How to Add Macro Code to Excel Workbook — How to copy Excel macro VBA code to your workbook, from website or sample file. Different types of code, where to paste it. Step-by-step videos, written steps

2 — Excel Macro to Save Sheets As PDF — Excel macro saves active sheet or sheets in PDF format, prompts for file name and folder. See macro code, get sample workbook with macro.

3 — How to Create Excel UserForm for Data Entry — See how to create an Excel UserForm for data entry, with text boxes and buttons. Follow step-by-step videos, detailed notes with screen shots, free Excel workbook

4 — Excel ComboBox Lists for Easy Data Entry — How to use an Excel ComboBox with a drop down list to make data entry easy in an Excel UserForm

Video: Record and Run Excel Macros

If you’re just getting started with Excel macros, this 5-minute video shows you the basic steps.

There are written steps, and an Excel workbook to download, on the Record and Test an Excel Macro page, on my Contextures site.

_______________________