2004 November | Automate Excel

Automate Excel

Nov 30
Excel Keyboard
icon1 Tom | icon2 Misc | icon4 11 30th, 2004| icon32 Comments »
excelkeyboard

A keyboard designed for Excel users that “Delivers an enhanced Microsoft

Nov 29

The following is a Macro to delete all of the Autoshapes on a given worksheet.

deleteautoshapes

Sub DeleteAllShapes()

'Activate sheet to delete autoshapes.
Sheet1.Activate

Dim GetShape As Shape

    For Each GetShape In ActiveSheet.Shapes
        GetShape.Delete
    Next

End Sub
Nov 28
News Network
icon1 Tom | icon2 Sites | icon4 11 28th, 2004| icon3No Comments »

Jon Wittwer of the Excel Nexus portal has just launched the Excel News Network weblog. The details.

“The purpose of this web log is to provide RSS newsfeeds to other sites related to Microsoft Excel that wish to have quality dynamic content. If you are an Excel consultant, this log can provide a way to spread your knowledge, make press releases, etc. This log will serve as a convenient method for announcing new Excel tips added to various websites (or tips recently discovered).”

Nov 28

Not having the ability to Undo more than 16 times in Excel can be a huge pain sometimes.

The Good News
There is a way to change this to 100!

The Bad News
It involves editing the Windows Registry.

This Microsoft KB Article will give you the details. The trick only takes a couple of minutes to implement and worked perfectly for me. However, I’ve reprinted the Warning message for those who haven’t edited the registry before:

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

Nov 27
VBA: Code Indenter
icon1 Tom | icon2 VBA | icon4 11 27th, 2004| icon31 Comment »

indentJohn Mansfield posted a few months ago about the Smart Indenter addin. I finally got around to a test driving it today.

If you need your code to be presentable, get sloppy with longer modules, or would like to see your code indented uniformly for the first time<g>, I’d recommend giving the Smart Indenter an evaluation(also it’s free).

Notes:
After installing, the Smart Indenter will be located under your Visual Basic Editor’s Edit menu.

The Smart Indenter gets a line item in “Add or Remove Programs”, located in the Control Panel, so uninstall is easy if it’s not for you.

The Smart Indenter comes with a handful of options that you can modify however there isn’t a reset button. So keep a mental note of the default options you modify because there is no obvious way to restore them.

The Smart Indenter doesn’t remove excess line breaks in code. Sure, the name didn’t suggest it would, however this would be a nice addition. I indent rather well but get liberal with line breaks occasionally.

Nov 26
Solitaire
icon1 Tom | icon2 Games | icon4 11 26th, 2004| icon32 Comments »

Add a Solitaire button to any toolbar, a quick game is just a click away!

On the Main Menu Bar
1. Tools
2. Customize
3. In the newly opened dialog box Select Tools in the Categories list
4. Scroll down the Commands list and you will see a deck of cards

solitaire

5. Click and drag the Card Deck icon to the toolbar of your liking.

Sidenote: I’m using Excel 2003 and have Windows Default games installed, I’m not sure of the availability on other setups.

Nov 25

To add a picture to a cell comment:

turkey

1. Right click the the cell that contains the comment and choose Edit Comment
2. Right click the edge of the comment and choose Format Comment
3. Select the Colors and Lines tab
4. Click the Color Dropdown box
5. Choose the Fill Effects Option
6. Click the Picture Tab
7. Select your picture, hit OK,OK

Nov 24
Portuguese Blog
icon1 Tom | icon2 Sites | icon4 11 24th, 2004| icon32 Comments »

If you also speak Portuguese here’s an Excel blog for you: BLOG of EXCEL in PORTUGUESE. If you don’t speak Portuguese(like me), but are still interested, you can translate it here.

I wonder if there is a newsreader that can translate feeds?

Nov 24

This is probably the fastest way to create a new workbook from within Excel, use the following shortcut:

Ctrl + N

To invoke the “open existing workbook” dialog box:

Ctrl + O

Nov 23
Heat map Tool
icon1 Tom | icon2 Templates | icon4 11 23rd, 2004| icon31 Comment »

“an MS Excel macro that takes a table of numbers and turns it into a colored heatmap, where the brightness/hue is proportional to the size of the number.”

Heatmap Tool (VBA)

« Previous Entries