Enter your email address:

Delivered by FeedBurner

Home Page








Subscribe here
Add to 

My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!

eXTReMe Tracker

  Wednesday, March 03, 2010 – Permalink –

Mail Excel

VBA + sample

Excel Guru Ron de Bruin has put together the VBA code needed to send Excel via email.
He has also included the sample workbooks for those that are not VBA literate.

". . . a new add-in named RDBMail for Excel/Outlook 2007-2010

The add-in create a new tab on the Ribbon named RDBMail with a lot of mail options.
You have the option to send as workbook or PDF for every mail option."

Code to send mail from Excel

See all Topics

Labels: ,

<Doug Klippert@ 3:43 AM

Comments: Post a Comment

  Saturday, February 20, 2010 – Permalink –

Selection Address

What's the count

". . . know what range is selected at any given time. You can look at the Name Box, but that only show the active cell."

Display number of Rows and Columns

See all Topics

Labels: , , , , , ,

<Doug Klippert@ 3:59 AM

Comments: Post a Comment

  Thursday, February 18, 2010 – Permalink –

Custom QAT

Access additions

Applications put most of the most-used commands on the Home tab's Ribbon, not everything is there. You may want to add Close, Close All, or Print commands, for example.

In the upper Left corner is the Quick Access Tool bar.

To update the QAT:
Click the down-pointing arrow to the right of the QAT.
Choose any common commands (New, Close, Print, etc.) by checking the option.

See all Topics


<Doug Klippert@ 3:31 AM

Comments: Post a Comment

  Friday, February 12, 2010 – Permalink –

Custom Toolbars

You’re not restricted

You can create your own toolbars. Here's some code that helps:

Toolbars for Fun and Profit

See all Topics

Labels: , ,

<Doug Klippert@ 3:09 AM

Comments: Post a Comment

  Tuesday, January 26, 2010 – Permalink –

Where's the Template

Find and/change storage spots

Describes the different template categories and the locations of templates in 2007 Office programs. Also describes the registry settings that control where to find your custom templates.

See all Topics

Labels: , ,

<Doug Klippert@ 3:48 AM

Comments: Post a Comment

  Wednesday, December 30, 2009 – Permalink –

Chart Null Data Gaps

Fill in the spaces

When creating a chart, data can sometimes be missing. You have a choice of leaving the cells empty or making them zero.

By default, an empty cell will leave gaps in your graph. Zero entries will send a line graph down to the base line.

One way to force Excel to interpolate the data is to enter the function =NA() in the empty cells.

Another way is to go to Tools>Options and choose the Chart tab.

Pick how you want data plotted - with gaps, as zero, or interpolated.

Microsoft also has this suggestion:

Gaps between the dates
"If the datasheet data for the category axis contains date number formatting, Microsoft Graph automatically uses a special type of axis in your chart called a time-scale axis.
A time-scale axis shows a blank category for dates for which you have no data. If you do not want to see these gaps — for example, if you have data for 1-Jan, 15-Jan, 3-Feb, 12-Feb, and 2-Mar, and you want to plot the days next to each other - you can change the time-scale axis to a standard category axis.
On the Chart menu, click Chart Options, click the Axes tab, and then click Category under Category (X) axis."

Note, For 2007+, on the Design tab, click Select Data in the Data area, and then click Hidden and Empty Cells on the Select Data dialog box.

See all Topics

Labels: , , , ,

<Doug Klippert@ 3:18 AM

Comments: Post a Comment

  Thursday, September 24, 2009 – Permalink –

Shut it All Down

Close the spreadsheet and Excel

As it comes out of the box, at this point, Excel 2007 is set to display spreadsheets on the task bar. No big deal 2003 did too.

If, however, you only have one workbook open and want the application to shut down when you close the book you must make a little change.

Just as before, you must deselect Windows in the Taskbar from the View Options.

In 2007 it is located by clicking on the logo icon, then choose Excel Options. The Windows entry is on the Personalize screen

See all Topics

Labels: ,

<Doug Klippert@ 3:44 AM

Comments: Post a Comment

  Wednesday, September 02, 2009 – Permalink –

Dynamic Tabs

Change tab names automatically

Changing the names of tabs is easy, just double click the tab or right click and choose rename.

Allen Wyatt has a small piece of code that will automatically update the tab name based on the value of a cell in the spreadsheet.

Sub myTabName()
ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub

Allen also has some error checking code on his site:

Dynamic Worksheet Tabs

Dick Kusleika suggests another way using a change event:

Naming a sheet based on a cell

See all Topics

Labels: , , , ,

<Doug Klippert@ 3:33 AM

Comments: Post a Comment

  Thursday, August 13, 2009 – Permalink –

Scroll Restrictions

Without Protection

You can protect a spreadsheet so that data is shielded from inadvertent entries.
There is another way to set up a scroll area that does not involve protection.
Open the Control Toolbox (right-click any existing tool bar) and click on the Properties icon.

In the Scroll Area text box, type the scroll area range, or type the defined Name for the range.
To cancel the Scroll Area restricted range, clear the Scroll Area text box.

If you are using Office 2007, the Property icon is on the Developers tab.

Multiple areas can be selected using Protection, but only one area is allowed using the Scroll Property.

See all Topics

Labels: , ,

<Doug Klippert@ 3:29 AM

Comments: Post a Comment

  Wednesday, March 04, 2009 – Permalink –

Animate Window Size

So cool!

The following macro has little or no practical computing value, but it can add a "way cool" element when a worksheet is unhidden.
There are three states that a worksheet can be in; Minimized, Maximized, and Normal.

This macro will gradually resize a worksheet from small to Maximized. The worksheet appears to be growing:

Sub SheetGrow()
Dim x As Integer
With ActiveWindow
.WindowState = xlNormal
.Top = 1
.Left = 1
.Height = 50
.Width = 50

For x = 50 To Application.UsableHeight
.Height = x
Next x

For x = 50 To Application.UsableWidth
.Width = x
Next x

.WindowState = xlMaximized
End With
End Sub

(By Mark William Wielgus)

Also fun:

Sub SheetGrow()

Dim x As Integer, xmax As Integer

With ActiveWindow

.WindowState = xlNormal

.Top = 1

.Left = 1

.Height = 50

.Width = 50

If Application.UsableHeight > Application.UsableWidth Then

xmax = Application.UsableHeight


xmax = Application.UsableWidth

End If

For x = 50 To xmax

If x <= Application.UsableHeight Then .Height = x

If x <= Application.UsableWidth Then .Width = x

Next x

.WindowState = xlMaximized

End With

End Sub

# posted by Joerd : 12/30/2005

See all Topics

Labels: , ,

<Doug Klippert@ 3:27 AM

Comments: Post a Comment

  Tuesday, February 10, 2009 – Permalink –

Dynamic AutoShape Link

Show the star

Here's a hint that I had forgotten about.

You can tie the result of a cell to an AutoShape.

This displays the value in a more dramatic manner.
  1. Create an AutoShape on the Worksheet
  2. With the shape selected, type an equal sign in the formula bar.
  3. Enter the address of the linking cell (or click the cell)
  4. Hit Enter
Thanks to for the reminder.

See all Topics

Labels: , ,

<Doug Klippert@ 3:21 AM

Comments: Post a Comment

  Saturday, October 11, 2008 – Permalink –

Stock Answer

Built in service

You can easily insert an automatically updated stock quote for a specific company in a spreadsheet.

Here are the instructions:

  1. On the Tools menu, click AutoCorrect options.
    (Use the Office button and Excel Options in 2007)
  2. Click the Smart Tags tab and place a check beside Label data with smart tags.
  3. Click OK.
  4. Type in a Stock symbol (such as MSFT) in a cell.
    (The symbol must be in all caps)
  5. Click outside of the cell.
  6. Place your mouse cursor over the purple triangle in the lower-right corner of the cell.
    Click the arrow.
  7. Click Insert refreshable stock price.
  8. Select whether to insert the stock price on a new worksheet or in a specific area on your current worksheet.

You can refresh a stock quote price at any time by right clicking anywhere within your worksheet and selecting Data Range Properties. Enter a value for how often the stock quote price should be refreshed.

See all Topics

Labels: ,

<Doug Klippert@ 4:20 AM

Comments: Post a Comment

  Sunday, September 14, 2008 – Permalink –

Add a Picture to a Comment

Also graphs

  1. Right-click the cell that contains the comment.
    (or choose Insert Comment)
  2. Choose Edit Comment, and clear any text from the comment.
  3. Click on the border of the comment, to select it.
  4. Choose Format>Comment
    (or Ctrl+1)
  5. On the Colors and Lines tab, click the drop-down arrow for color.
  6. Click Fill Effects
  7. On the picture tab, click Select Picture
  8. Locate and select the picture
  9. Click Insert, click OK, click OK


Charles Maxson has some code that will place a picture in the comment box based on the contents of a cell.

" Imagine that you have a list of parts for a product and you want to assign them to cells... then your users could see them as they hover over the cells."

Excel: Code to add picture to Excel Comment

Mary Ann Richardson, on, suggests saving a graph as a JPG and using that as a picture in a comment.

Add an Excel chart to a comment

See all Topics

Labels: ,

<Doug Klippert@ 2:48 AM

Comments: Post a Comment

  Sunday, June 29, 2008 – Permalink –

Customize Date in Footer


This subroutine inserts the current date in the footer of all sheets in the active workbook. This process can be accomplished without a macro, however, you'll need the macro if you want to specify the formatting of the current date. An example of the return generated by running this macro is Saturday, March 05, 2005.

Sub PutCurrentDateInCenterFooterAllSheetsInWorkbook()
For Each oSheet In ActiveWorkbook.Sheets
oSheet.PageSetup.CenterFooter = Format(Now(), "dddd mmmm dd, yyyy")
End Sub

You can change the word CenterFooter to CenterHeader. You could also use LeftHeader, RightHeader, LeftFooter, or RightFooter.

Microsoft KnowledgeBase:
Macro to Change the Date/Time Format in a Header/Footer

See all Topics

Labels: , ,

<Doug Klippert@ 3:11 AM

Comments: Post a Comment

  Sunday, May 04, 2008 – Permalink –

Conditional Formatting

If it's Tuesday, it must be mauve

Conditional formatting is one of Excel's better features. It allows you to preset certain font styles, colors, and cell-background colors based on cell values.

This can be very useful for highlighting important information and values outside an accepted range or providing a visual cue to associate value ranges with color codes.

The best part is that conditional formatting is very easy to set up.

Just click the cells you'd like to format and select Format >Conditional Formatting. The Conditional Formatting dialog box lets you set up the conditions by which the formatting of the cell will occur.

You pick the operator (between, equal to, less than, etc.) and the value or range of values. Click Format to open the Format Cells dialog box, where you can select the colors and styles to be used.

Each cell can have several conditional formats. For example, you might say that if a certain cell's value is between 20 and 50, the text should be blue on a yellow background.

However, you can format that same cell to exhibit red, bolded text on a green background if it contains a value between 51 and 100.

Conditional Formatting

Before 2007, you could use up to three conditions, but earlier versions of Excel can be tricked to use more if it should become necessary.

Oz Grid:
Excel VBA Macro Code to Get Around Excel's 3 Criteria Limit in Conditional Formatting

GR Business Process Solutions:
Graham Barrow and Ray Blake
Highlight the current or past month in Excel with conditional formatting

Chip Pearson:
Conditional Formatting


Compatibility in Excel 2007

Conditional Format Functions in Excel 2007

Adding Customized Rules to Excel 2007

Data takes shape with conditional formatting

See all Topics

Labels: , , ,

<Doug Klippert@ 7:03 AM

Comments: Post a Comment

  Saturday, March 22, 2008 – Permalink –

Default Save

Choose your own location

When you choose to save most Office files, the Save dialog box defaults to the Documents or My Documents folder.

(The following directions work in 2007, but you need to click on the Office button in the upper left corner of the Window)

you can change the default location by going to Tools>Options. On the "File Locations" tab you can modify the storage location.

Tools>Options. On the "General" tab change the default location.

uses Tools>Options and the "Save" tab.

Tools>Options and the "General" tab for Databases and Projects

Tools>Options "General".

will make you take an underground tour into the Registry to change the location to save e-mail attachments.

FrontPage/Expression Web
appears to require the same sort of spelunking.

Change the folder where e-mail messages and attachments are saved

D.C. Everest school district Weston, WI:
Office Default Paths

If you don't want to change the default, but would like to be able to quickly go to an alternate site, open the Save or Save Attachment dialog box. On the left side of the box is the Places Navigation bar. If you click the Desktop icon, that location will be used to save the file.

You can add spots to the bar. Browse to the specific folder. Highlight the folder and click the down arrow beside the Tools option. Select "Add to My Places."

The file or e-mail attachment can then be saved where you want.

See all Topics

Labels: ,

<Doug Klippert@ 7:45 AM

Comments: Post a Comment

  Wednesday, September 19, 2007 – Permalink –

Clippy's Revenge pre-2007

Roll your own OA

The Office Assistant has taken a lot of hits, but it is missing in 2007, but still around in earlier versions.

If you want to play with it, see John Walkenbach's Create A Fake Clippy

Here is a creature you could use to replace Clippy:
Nerd Bird

There are other articles in the MS library about Agents and Assistants:
Animating Office Applications with Microsoft Agent

Using the Office Assistant to Display Help

Programming the Office Assistant

The VBA help file give a list of motions for the assistants. Look for Assistants>Animation.
The code is simple:

With Assistant
.On = True
.Visible = True
.Move xLeft:= 400, yTop:= 300
.MoveWhenInTheWay = True
.TipOfDay = True
.Animation = msoAnimationGreeting
End With

Here are a few of the actions:

  • msoAnimationBeginSpeaking
  • msoAnimationCheckingSomething
  • msoAnimationGetArtsy
  • msoAnimationGetTechy
  • msoAnimationGetWizardy
  • msoAnimationRestPose
  • msoAnimationSendingMail
  • msoAnimationThinking
  • msoAnimationWorkingAtSomething
  • msoAnimationWritingNotingSomething
  • msoAnimationCheckingSomething
  • msoAnimationGetTechy
  • msoAnimationListensToComputer

See all Topics

Labels: ,

<Doug Klippert@ 7:45 AM

Comments: Post a Comment

  Friday, August 03, 2007 – Permalink –

Undo Excel

Level talk

In Excel 2007. the number of levels of the "undo stack" was increased from 16 levels to 100.

Setting AutoFilters, showing/hiding detail in PivotTables, and grouping/ungrouping in PivotTables are now reversable.

And the undo stack is not cleared when Excel saves, be it an AutoSave or a Save by the user.

If you think the number of undos should be changed, here's how:

  1. Close any programs that are running.

  2. Click Start, click Run, type regedit in the Open box, and then click OK.

  3. In Registry Editor, expand one of the following registry subkeys, as appropriate for the version of Excel that you are running:

    Microsoft Office Excel 2007

    Microsoft Office Excel 2003 uses Office\11.0\
    Microsoft Excel 2002 uses Office\10.0\
    Microsoft Excel 2000 uses Office\9.0\

  4. On the Edit menu, point to New, and then click DWORD Value. Select New Value #1, type UndoHistory, and then press ENTER.

  5. On the Edit menu, click Modify.

  6. In the Edit DWORD Value dialog box, click Decimal under Base. Type a value between 0 and 100 in the Value box, click OK, and then exit Registry Editor.

  7. Start Excel. Excel stores an undo history for the number of actions that you specified in step 6.

Modify the number of undo levels

If you want to clear the undo stack, just run a macro such as:
Sub ClearUndo()
Range("A1").Copy Range("A1")
End Sub

Allen Wyatt:
Clearing the Undo stack

See all Topics

Labels: , , ,

<Doug Klippert@ 6:36 AM

Comments: Post a Comment

  Friday, June 08, 2007 – Permalink –

Location Indicator

Point to the spot

Here's a link to the code that produces conditional formatting on the fly to the cells in the current row and column.

Color banding location

See all Topics

Labels: , ,

<Doug Klippert@ 5:55 AM

Comments: Post a Comment

  Saturday, March 24, 2007 – Permalink –

Splash Screens

A nice sparkle

After working hours to perfect an Excel project, the last little piece that adds a touch of class, is a splash screen.

Here are the instructions to construct this type of user form including a workbook that you can download:
Userform - Splash screens

Ivan F Moala administers this interesting site called The Xcel Files

See all Topics

Labels: , ,

<Doug Klippert@ 5:26 AM

Comments: Post a Comment

  Tuesday, February 06, 2007 – Permalink –

Customize the 2007 Ribbon

Let the add-ins begin

It is said that the Office 2007 Graphical User Interface Ribbon cannot be as easily changed or modified like it has been in previous versions.

This may be partially true, but not all is lost.

Here is some information from the equine's mouth:

Learn how to customize the Ribbon user interface (UI) in the 2007 Microsoft Office release. Also learn how new features in Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System support RAD development of Ribbon customizations. (40 printed pages)

Customizing the Office (2007) Ribbon

Monsieurs MS also have a downloadable spreadsheets with the Control IDs. There are files for 2003 as well.

Lists of Control IDs

2007 Office System Add-In: Icons Gallery

See all Topics


<Doug Klippert@ 7:53 AM

Comments: Post a Comment