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.
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.
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
Right-click on the graphic that you want to add to the desktop.
Click on Set as Desktop Background
From the Position drop down, select an option. (Center is my preference.)
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.
Double-click the My Computer icon
Click the Tools menu, then click Folder Options
Click the General tab
Under ‘Click items as follows’, click ‘Single-click to open an item (point to select)
Select the formatting option that you prefer (I don’t like underlining, so I selected ‘Underline icon titles only when I point at them’)
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.
To open a second window:
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.
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:
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.
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.
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. 😉
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.
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.
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
View the Worksheet Code
To see the code for a worksheet, right-click on its sheet tab, and click View Code.
The Visual Basic Editor opens, and shows the code for the active sheet.
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.
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.
______________________
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 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.
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.
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.
“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.
________________
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.
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