VBA + sample

". . . a new add-in named RDBMail for Excel/Outlook 2007-2010
http://www.rondebruin.nl/mail/add-in.htm

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."

Labels: Customize, VBA

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."

Labels: Customize, Formulas, Macros, Reference, Shortcuts, Tips, VBA

Access additions

Labels: Customize

You’re not restricted

Labels: Customize, Ribbon, VBA

Find and/change storage spots

Labels: Customize, General, Templates

Fill in the spaces

"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."

Labels: Charts, Customize, Formats, Formulas, Tips

Close the spreadsheet and Excel

Labels: Customize, Tips

Change tab names automatically

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

Labels: Customize, Formats, Macros, Tips, VBA

Without Protection

Labels: Customize, General, Tips

So cool!

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

Labels: Customize, Tips, VBA

Show the star

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

Labels: Customize, Link, Tips

Built in service

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.

Labels: Customize, General

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
   

Also:

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 TechRepublic.com, 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
excel

Labels: Customize, Tips

Formatting

Sub PutCurrentDateInCenterFooterAllSheetsInWorkbook()
For Each oSheet In ActiveWorkbook.Sheets
oSheet.PageSetup.CenterFooter = Format(Now(), "dddd mmmm dd, yyyy")
Next
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
excel

Labels: Customize, Formats, VBA

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.




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

Also

Compatibility in Excel 2007

Conditional Format Functions in Excel 2007

Adding Customized Rules to Excel 2007

Demo:
Data takes shape with conditional formatting



See all Topics
excel

Labels: Customize, Formats, Tips, Tutorials

Choose your own location

Word

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

Excel

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

PowerPoint

uses Tools>Options and the "Save" tab.

Access

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

Publisher

Tools>Options "General".

Outlook

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.

Labels: Customize, General

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

And:
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

Labels: Customize, Macros

Level talk

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
   

   HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options

   
   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.

Sub ClearUndo()
    Range("A1").Copy Range("A1")
End Sub

Labels: Customize, General, Macros, Tips

Point to the spot

Labels: Customize, Formats, Tips

A nice sparkle

Labels: Customize, Gadgets, Macros

Let the add-ins begin

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)

Labels: Customize