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)

Quote from Ken:

I have a request. I am currently writing the book

You can link Autoshapes that can contain text to a cell. The text in the Autoshape will change when the cell is updated.

1. Draw an Autoshape on your spreadsheet
2. In the formula bar type an equals sign (Make sure the Autoshape is still selected)
3. Click the cell you want to link to the Autoshape and hit Enter
4. Add formatting to your Autoshape

Excel Hacks takes number 1 on the 2004 Amazon’s Top 10 Editors’ Picks: Computers & Internet.

“SourceForge.net is the world’s largest Open Source software development website, with the largest repository of Open Source code and applications available on the Internet.”

This is probably not the first site that pops into my mind when surfing for Excel, however a search for VBA or Excel turns up a few projects. Like this addin to compare two workbooks.

You can tile an image as a background in a worksheet by doing the following:

1. On the Main Menu click Format
2. Select Sheet
3. Select Background
4. Choose your image

I used an old school brick background in the following picture.

No Brainer - Post to Excel Blog from Excel
It seems obvious that I should be posting about Excel from Excel, why not? However, I’m not sure of the details yet.

My blogging software accepts posting by email, so I may devise a way to post to this blog from Excel by sending an email from Excel. Or possibly just connect directly to the MySQL database from Excel? I’m not sure how to synch up a picture upload with either scenario, so possibly just quick notes.

But enough about me…
…what about you? I’ll get to a practical idea shortly, but first:

Let’s assume I create this “addin” for Excel that allows me to post to this blog, couldn’t I then let you download the addin and post to this blog? Not that I want tons of people posting here, but we could use another domain name, and make it 100% ad free so people are the most comfortable sharing. You could then view the thoughts and notes of Excel users, about Excel, while they actually work in Excel. Almost like del.icio.us, however our bookmarks are Excel notes, and our application is Excel and not the browser.

And the practical idea(maybe:-)
While thinking about posting tips and notes from inside Excel to the web, I thought of the question “What would be the exact opposite of this?”, and the answer was “Ask a question from inside Excel”. (taking instead of giving)

Why haven’t the forum owners or the newsgroups evolved to this model, tried it, or made it an alternative method of using their sites?(maybe they have?) Why does somebody have to drop all trains of thought and leave Excel, launch their browser, log onto their forum, ask a question, and then keep returning to the site to look for an answer?

Maybe a better solution would be an Excel addin that would allow you to ask a question from within Excel, send it off into space and you could continue working, when an answer was provided it would automagically alert you(rss?), and finally let you view the answer or respond to it from within Excel.

Start from scratch? Hack the discussion server front end(which I’ve never used)? Expose a forum as a web service and build a forum smart client addin?

Conclusion
-These ideas may or may not be intelligible, completely formed, or come to fruition.
-These ideas could probably be applied to any desktop app, not only Excel.
-Just kicking some ideas around and thought I’d share.

Adam Squire has posted a creative way of displaying fourteen different charts in one. It uses one chart object and a data validation dropdown list to change the data in the chart(you’ll need to download the workbook).

If you have ever found yourself with a ridiculous amount of charts in a workbook, this could possibly be a valuable example.

Tired of the default color palette?

Would you prefer some of these choices?

If you have Excel 2002 or higher the option is now available to you. Here’s how:

1. Goto Tools
2. Goto Options
3. Select the Color Tab

4. Click the color you would like to replace in the OLD color palette
5. Click Modify and select your new color, hit OK, OK

Back in your spreadhsheet you will see your newly selected color in place of the old, for instance when choosing a paint bucket color.

TurboExcel to fight back!

… I saw the weather man start from a view way out, click and drag a square on the map, and drill down to our viewing area. Just like he always does.

Tonight however I had the thought - “Wow, that would be a cool feature for an Excel chart”. I’m not sure if the same implementation would be preferable, possibly the drill down area could enlarge in a pop-up window? Possibly a Zoom for a chart object and not the entire sheet?