November 16, 2006

Map Maker

Just something I made on a whim. There are times when you want to draw a simple map, and when you think about how you can set Excel's rows and columns to a grid, it's a perfect choice for the job.

Enter the Map Maker. Push the "map" butttons to insert bitmap images into selected cells. (Rows and Columns are sized to fit the bitmaps) You can launch it from the Tool menu at the top of Excel once the add-in is installed.

Okay, the butttons don't look so nice yet but I did this in a hurry :-)

You will notice the arrow buttons on the userform. They work in the same way as the buttons mentioned in my previous post about detecting the Ctrl and Shift keys. Push the buttons by themselves to select adjoining rows and columns, or use while pushing down the Ctrl or Shift keys to expand or contact selected ranges accordingly.

Here's an example of what you can do. (Background color was added by selecting the shapes and changing the Fill color to Light Green)

Once the map bitmaps are inserted, you can take advantage of Excel's inbuilt drawing features. The "building" and sign that says "Here" in the above image are standard Autoshapes, sized and formatted to suit.

It's fun!

Tested on Excel 97 and 2003 (modeless userform for Excel 2000 and later versions). The bitmaps come out the wrong size in 2007, but I will be looking for a workaround and upload it when available.

You can download it here ;-)  

Posted by andrewe at 21:59

November 09, 2006

Detecting the Ctrl and Shift Keys

Something I find a chore - the information you refer to is in one column, but the column you want to edit is across the other side of the screen. How to get to the edit column and select the exact same rows? Not so bad if you just have a few groups of cells to work with, but if you have a lot - say 100 and not all visible on the screen, it becomes significantly harder. (One thing you can use is use a filter, but that does take a while to set up). So I added this feature to a previous version of my New Book Navigator Toolbar add-in.

Some cells selected in Column H, I push "Select Next Right Column"...

Now the selected cells are in Column I. (The cells themselves have not moved, just the column to the right has been selected with the same rows)

It's quite quick and handy to use, just keep pushing the button until you reach the column you want. But there are other times when I want to select other columns but still retain the originally selected cells. (Like using Shift and the arrows keys but for more than one area as above). I could add some more buttons, but it's just as easy to make the buttons multi-purpose.

This is where GetKeyState comes into play as I can use it detect whether certain keys are pushed or not at the same time as the buttons on my toolbar.

At the top of a module I write this.

Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
Private Const VK_CTRL As Integer = &H11

Now, within the Select Next Right Column macro itself I write something like this,

If GetKeyState(VK_CTRL) < 0 Then
    ...some code...
End If

Now when I push the Ctrl key, the code in my add-in retains the originally selected cells plus the adjoining column. (The logic behind using the Ctrl key is that I use it anyway to select multiple areas)

This is handy too, but what if I select too many cells? Start over? Not
likely, I just use more code to make the button deselect the excess cells.

First, I add an additional line to the top of the module,

Private Const VK_SHIFT As Integer = &H10

And use this within the macro,

If GetKeyState(VK_SHIFT) < 0 Then
    ...some more code...
End If

In this case, I have used Shift as a "reverse" key, similar to keyboard combinations that work opposite to normal when the Shift key is pushed (eg Shift and Tab)

I've just saved myself from adding 8 new buttons (2 for each direction), plus the fact I can just push some keys while using the same buttons on the toolbar to expand or contact the ranges makes it a lot more user friendly.

Anyway, the updated version is here. You'll find some other new stuff has been added recently also ;-)  
Posted by andrewe at 20:58

November 03, 2006


The new version of our utilities is almost complete. In the meantime, I have been working on some new stuff. Something that I had fun with was a way to make working with Marlett checkboxes easier, a new addin.

Select a range and push Add Formatted Checkboxes to Range and the colors shown above will appear. Choose a color and the checkboxes will be added to the selected cells. At the same time, the code to make the checkboxes work will be added to your workbook so it will be okay even if you uninstall the addin at a later date, the code inside the workbook will run independently. You can also change the checkbox colors by selecting the same cells and choosing a new color.

Other options - Select, Delete, Tick and Untick require you to select cells that contain checkboxes as shown below (Don't select the shapes above the checkbox cells, they merely act as a way to activate the code to show the checkoxes as ticked or unticked)

Note: I've tested on Excel 97, 2003 and 2007 and it works okay. Depending on your security settings, you many need to ensure access to the Visual Basic Project is enabled. Instructions to do so are shown in the addin Read Me file.

Code for the addin is unlocked so you can see how things work. If you want to experiment working with the Visual Basic Editor, you will need to set a reference to the Visual Basic Project - make sure the project you are working on is selected and go to Tools, References and check Microsoft Visual Basic for Applications Extensibility on the VBE, (not the Tools Menu within Excel itself, the Visual Basic Editor!)

You can download it here :-)  
Posted by andrewe at 19:20