Automate Excel

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

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

Sub DeleteAllShapes()

'Activate sheet to delete autoshapes.
Sheet1.Activate

Dim GetShape As Shape

    For Each GetShape In ActiveSheet.Shapes
        GetShape.Delete
    Next

End Sub

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).”

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.

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

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

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.

To add a picture to a cell comment:

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

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?

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

“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)