Flowcharting in Excel Series -
How to Insert Custom Graphics in Excel Diagrams
By Nicholas Hebb
Excel Flowchart Wizard
FlowBreeze - a flowcharting automation add-in for Excel that makes creating flowcharts simple and pain free. Download a Free 30-day Trial Today.
Microsoft Excel contains 124 built-in symbols in most versions, with even more added in Excel 2007. These are sufficient for most flowcharts and other types of process flow diagrams, but there are times when additional symbols would be useful. This article shows one a way to automate adding custom graphics to your Excel diagrams.
This article will show how to create macros for automatically inserting the graphics and then how to create menu items to access the symbols quickly. Note, however, that this article covers creating macros and a custom menu. Creating custom ribbon items for Excel 2007 is more complex and beyond the scope of this article.
Part 1 - Create the Macros
1. Create a directory for your graphics and store all the graphics files in it. Note: scroll down for an example on how to use Visio symbols for this.
If you create your own graphics, keep in mind that Excel displays at a default of 96 pixels per inch (PPI). You can change this setting as follows:
- Click Tools > Options from the Excel menu.
- Click the General tab.
- Click the Web Options button. This will open a new dialog.
- Click the Pictures tab.
- Type in the new "Pixels per inch" setting (300 max).
2. In Excel, go to Tools > Macro > Visual Basic Editor.
3. In the Visual basic Editor (aka, VBE) right-click on Personal.xls and select Insert > Module from the context menu, as show in the picture below.
4. In the new module, add the following code:
Sub Insert<PICTURE_NAME>Picture()
ActiveSheet.Pictures.Insert("<PATH>\<FILE_NAME>").Select
End Sub
5. Copy and rename the <PICTURE_NAME> and <FILE_PATH>\<FILE_NAME> sections of the code for each picture. Then close the Visual Basic Editor and exit Excel. It will ask if you want to save personal.xls. Click Yes, then re-open Excel. (This is just a way to make sure the macros get saved.)
Part 2 - Create the Menus
6. On the Excel menu, click View > Toolbars > Customize. This will open the Customize window.
7. On the Customize window, select the Commands tab, then scroll to the bottom of the Categories list and select New Menu (see picture below). From the right panel, drag and drop the New Menu item onto the Excel menu.
8. Right-click on the new menu item to access its properties. Rename the menu something appropriate. You can give the menu a keyboard shortcut by but an ampersand (&) in front of a letter. Make sure not to use a shortcut latter that is already used by one of the existing top level menu items. In the picture below, the menu is renamed "Insert Graphics" with the ampersand placed in front of the "G".
9. Next, select Macros from the Categories list on the Customize window. From the right panel drag and drop the Custom Menu Item item onto the new dropdown menu. Add one for each macro you created in steps 4-5.
10. Right-click on each new menu item and select Assign Macro from the context menu (this menu will also allow you to assign text or edit the button image). Select one of the macros from personal.xls module. Make sure to assign a shortcut key with an ampersand for quick keyboard access.
Repeat Steps 9 & 10 for each "insert graphic" macro.
Close out the Customize window, and now whenever you select one of the menu items, it will insert the associated image in the current cell. You can run arrow connectors to them just as you would a normal autoshape.
A Special Tip for Visio Users
This section is for Visio users. It assumes that you have a valid license for Visio and aren't violating any Microsoft copyrights by copying the graphics from Visio to Excel.
It may surprise some people, but many Visio users also like to use Excel to create diagrams. Obviously, Excel doesn't offer all the diagramming capabilities of Visio, but it does let other Office users edit and maintain the drawing without having to buy expensive, specialized software like Visio. Moreover, it offers one thing that Visio doesn't - the ability to combine data and graphs with diagrams.
To use Visio graphics in Excel, you can simply copy and paste them into Excel. But, then they are embedded as OLE Objects, which means they can be edited in Visio by double-clicking them. Otherwise they show up as pictures. But more to the point of this article, you would need to copy and paste them from Visio every time you wanted to use them in Excel. This trick gets around that hassle. So another way to use Visio graphics is to capture them as bitmaps, then import the Visio symbols into Excel as picture objects.
To do so, create a new drawing in Visio and drag all the desired shapes out onto the canvas. For each shape, copy it into Microsoft Paint (typically found under Accessories in your Start menu), then save each one as a bitmap file (PNG format is another option, but GIF and JPG usually don't produce nice results.)
Then you can use the technique described in steps 1-10, above, to automatically insert them into your Excel diagrams whenever you want.
About the Author
Nicholas Hebb is the founder of BreezeTree Software, maker of FlowBreeze Flowchart Software.