Pie Chart Style Status Icons

Today a visitor stopped by the blog and sent me this message via my MeeboMe chat window:

[14:40] meeboguest######: I'm trying to insert a 4 section circle icon that is shaded in different ways. I'm using it to track progress on different projects. So I need a total of five different icons. completely unshaded, 1/4 shaded, 1/2 shaded, 3/4 shaded, and 4/4 shaded. where do i find these icons?

Sadly, I was away from my computer at the time. Here's my suggestion anyway:

I don't know of a good set, but these are nice to have for your dashboards and status reports. I say just create your own. Here's a simple set I created in 5 minutes using Microsoft Paint. I'm sure others among you are far more creative, so feel free to comment and share your icon collections.

Combine these images with my post on Pulling Images in Excel, and you could have a pretty slick status report. Be sure to remember the work-around when using Excel 2007.

Enjoy...

Excel_Geek

Pulling Images into Excel from Websites

You may recall a post I did just over a year ago about pulling images into Excel. That project involved pulling images from directories on your computer. I just completed another $50 Project where the client wanted to pull images into Excel from a website. Really the process was the same, except that this client is using Excel 2007, so I had to head Jonah's advice (see comment on that last project's post) and instead of directly pull the images using

ActiveSheet.Pictures.Insert().Select

I had to first draw an appropriately sized rectangle, move it to the appropriate cell, and "pull the image" by making it the background fill for that rectangle. Maybe someday Microsoft will fix this little idiosyncrasy in Excel 2007, but we'll not hold our breaths.

In the end it's a nice little file. Insiders, your file's on it's way.

Later,

Excel_Geek

Pulling Images into Excel

The other day I did a quick $50 Project for a guy who asked if there was a way to automatically pull images into a spreadsheet based upon a values in a range of cells...you know, like for an inventory list or something.

Sure.

Actually, the VBA code involved is quite simple -- one line really. Let's say that the value in the cells is actually the name of the image (only without the file type extension) that you want to pull in. That code might look like this:

ActiveSheet.Pictures.Insert(<IMAGE_DIRECTORY> & "\" & cel.Value & ".jpg").Select

Pretty simple. Now, one tip that I'd pass along when working with images or other similar objects via VBA in Excel is to immediately rename the object, since it can become cumbersome trying to figure out if it's called shape1 or shape2 or shape 300, etc. So I would rename the image to that same value in the cell, so it's easy to reselect later if you need to:

Selection.Name = cel.Value

It does get a bit more involved, though, as you probably want the images to fit nicely in a certain column and/or row. To do this you first have to determine what the height and/or width is (and adjust it if you wish) and then determine what the dimensions of the image you've imported are and adjust them to fit. Also, you probably would want to check to see if an image already exists and delete and replace it.

The code to do these things is pretty straightforward. If you'd like to learn how to do these sort of things, I'd encourage you to record these sort of actions using the macro recorder in Excel and then look at the code and see how it works. Actually, that's the way I'd suggest you learn pretty much anything VBA in Excel. That's what I do...

I added some of this other functionality to the file, which, Excel_Geek Insiders, is on it's way.

Later,

Excel_Geek