March 31, 2005

Checkboxes 3

Here's some more stuff you can do with "Marlett" checkboxes. (You can do similar stuff with regular checkboxes, I just prefer working with "Marlett" ones for the time being)

Adding Labels
If you used my macro below to add "Marlett" checkboxes to your worksheet, it's easier to enter text into the transparent shapes like this. (I added some spaces to get the text aligned, there's no indent for AutoShapes)

These are relatively simple changes, you could do all kinds of weird and wonderful things changing the Conditional Formatting, Font color etc with the macro itself. If you know how to, let your imagination run wild :-)

Getting Ticked Off
Here's a simple Workbook_Open macro to with an option to untick all checkboxes at once. Place it in ThisWorkbook with the Visual Basic Editor.

Private Sub Workbook_Open()
    Dim c As Range, Proceed As Long
    Proceed = MsgBox("Do you want to untick all checkboxes? ", vbYesNo)
    If Proceed = vbYes Then
        For Each c In ActiveSheet.UsedRange
            If c.Font.Name = "Marlett" And c.Value = "a" Then c.Value = ""
    End If
End Sub

Making Them Go Away
Let's say you want to hide the checkboxes when printing (without Labels in this case).

Add this to the my AddCheckboxesMarlett code (the next line after where it says "With Selection" is fine).

.PrintObject = False

Or if you already have some checkboxes, use the Select Objects arrow on the Drawing toolbar to select them, right-click any checkbox and select Format AutoShape, Properties, then untick Print object.

Now the boxes are hidden, use this code to hide the ticks, placing it in ThisWorkbook (Idea "borrowed" from the Daily Dose of Excel. Sorry Dick!) The checkboxes will reappear after being printed.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim c As Range
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Cancel = True
    For Each c In ActiveSheet.UsedRange
        If c.Font.Name = "Marlett" Then
            c.Font.ColorIndex = 2 'White
            c.FormatConditions(1).Interior.ColorIndex = 2 'White
        End If
    For Each c In ActiveSheet.UsedRange
        c.FormatConditions(1).Interior.ColorIndex = 6 'Yellow
        c.Font.ColorIndex = 0 'Black
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

I had to use the printer at work a few times before I got it right. People were giving me strange looks, little do they suspect I'm an Excel addict and a maniac ;-)  

Posted by andrewe at 21:24

March 29, 2005

New Look

Yes, there's a new look for my main site, courtesy of my buddy Masaru Kaji, Microsoft Excel MVP (or more commonly know to his fans and friends - Colo, the VBA Guy!)

It started like this...Gareth, co-conspirator of the JMT Forum asked for new logo suggestions for his site, The Excel Maniacs.

Hmm, about this?

In the meantime, Colo was working on his own logo as you can see in the top right hand corner of the already updated "Maniacs" link.

Well, waste not, want not... now look what happened...

First an addict, and now a maniac, things are really looking up! ;-)  
Posted by andrewe at 19:25

March 28, 2005

Checkboxes 2

Yesterday I wrote about an alternative to checkboxes using Marlett font. After experimenting with "regular" checkboxes for so long, I kind of missed being able to just click them on or off.

So I wrote this based on Application.Caller and OnAction. Over each cell in a selection, a clickable tranparent rectange is placed, and when clicked it turns color on and off.

Sub AddCheckboxesMarlett()
    On Error Resume Next
    Dim c As Range, myRange As Range
    Set c = ActiveCell
    Set myRange = Selection
    For Each c In myRange
        With c
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
            .FormatConditions(1).Interior.ColorIndex = 6
            .Font.Name = "Marlett"
        End With
        ActiveSheet.Shapes.AddShape(msoShapeRectangle, c.Left, c.Top, c.Width, c.Height).Select
        With Selection
            .ShapeRange.Fill.Visible = msoFalse
            .Name = c.Address
            .OnAction = "ToggleCheckbox"
        End With
End Sub

Sub ToggleCheckbox()
    On Error Resume Next
    Dim shp As String
    shp = Application.Caller
    If Range(shp).Value = "a" Then Range(shp).Value = "" Else Range(shp).Value = "a"
End Sub

Here's how it looks.

Advantages over "regular" checkboxes? Well, if you have a lot in a column, you can select the entire column and push Delete to reset them all at once. Other than that, it was an interesting experiment.

More Checkbox stuff next time ;-)  
Posted by andrewe at 22:37

March 27, 2005


I've used checkboxes (Forms toolbar) for a while now but always found them a little awkward. For example, if there are a lot of them on a Worksheet with a white background, they don't stand out very much and it's a bit hard to check the checkboxes (pun intended).

Here's a way to make them a bit more visible. (Be forewarned this will remove Conditional Formatting and use White font in the cells you select)

A little background first. All checkboxes can link to a cell (Optional). If you designate a linked cell (by right-clicking a Checkbox to bring up the Format Control dialog box), if ticked, the link cell returns TRUE, otherwise it will return FALSE. You can also change it's name using the Name Box.

Notice how I use the singular tense ("a" Checkbox, "it's" name). This is because they must be formatted one at a time. This really is annoying when you have several checkboxes you want to add or change.

This macro does it automatically by giving referencing each checkbox to each cell in a selection, both linked cell and the checkbox name. It also changes color to Yellow when ticked and back to White when unticked, using Conditional Formatting (just change the color index to get other colors if you prefer)

Sub AddCheckBoxes()
    On Error Resume Next
    Dim c As Range, myRange As Range
    Set myRange = Selection
    For Each c In myRange.Cells
        ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
            With Selection
                .LinkedCell = c.Address
                .Characters.Text = ""
                .Name = c.Address
            End With
            With Selection
                .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="=" & c.Address & "=TRUE"
                .FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked
                .FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked
                .Font.ColorIndex = 2 'cell background color = White
            End With
End Sub

This is how they will look.

Note: A non-VBA alternative is to use Marlett font, then type "a". This will do the same thing and you can use Conditional Formatting to change the color also.

Just so you know, checkboxes can be set not to print using the Properties tag of the Format Control dialog box. Use the Select Objects arrow on the Drawing toolbar to select them as you want.  
Posted by andrewe at 19:54

March 23, 2005

Refer by Month

The other day, there was a question at the JMT Forum about referring to certain sheets by month.

My solution was this. First, the sheet names are set up with the first three letters of the months and the last two digits of the current year, with the exception of the last sheet which is used to refer to the month sheets in question.

Then I use this formula (modified to work with a Combo Box, this way you can show various monthly summaries without having to constantly change sheets)

=INDIRECT(CHOOSE($D$2,"Jan","Feb","Mar","Apr","Jun","Jul","Aug ","Sep","Oct","Nov","Dec")&TEXT(YEAR(TODAY()),"yy")&"!"&CELL("address",B2))

The cell B2 is used to refer to a corresponding cell in each month name sheet and the cell D2 is being used with a Combo Box like below to select month names shown in cells F3:F5.

Note the dollar signs in the formula which mean that cell D2 is always referred to, what's called an absolute reference, whereas cell B2 with a relative reference can be dragged or copied into adjoining cells to match their counterparts in the sheet that is being selected.

You can find more information about absolute and relative referencing under "About cell and range references"in your Help files.  
Posted by andrewe at 22:04

March 20, 2005

Office Assistant 2

I've been experimenting with the Office Assistant again...

I don't like the OK button that appears in the text balloon shown in my post last week, and it seems there is no way to switch it off (or is there?). Anyway, this is my "silly" way to get around the problem.

Make your own Assistant
Did you know you can use Excel as an Image Editor? Let's try making a screen capture first. Go to Tools, Options, View and uncheck Gridlines. Then go to Help, Show the Office Assitant if not visible already.

Time for the screen capture. On my keyboard, I use Fn (Function) and Prt Scr (Print Screen). Paste anywhere you like.

Click the resulting image, then use the Crop tool on the Picture toolbar to resize the image (if not visible, go to Tools, Customize or just right click any other toolbar). When finished, push the Set Transparent Color button (second from right) to make the area surrounding the Assistant transparent. (See "Create transparent areas in a picture" in your Excel Help files if you get stuck)

With the image clicked, go to the Name Box and type "myAssistant", then push Enter.

Now make a callout (speech balloon) from Autoshapes, Callout on the Drawing toolbar. Name the callout "myCallout". Place it next to "myAssistant", so that it appears he/she/it is talking.

Your preparations should now be complete. Here's some code to make him/her/it tell you the coordinates of the top, left corner of the active cell. (You might have to adjust some of the numbers to get the right positioning)

Sub CellCoordinates()
On Error Resume Next
    Dim myLeft As Long, myTop As Long
    Dim myCell As Range
    myLeft = ActiveCell.Left
    myTop = ActiveCell.Top
    Set myCell = ActiveCell
    If ActiveCell.Top <= 60 Then myTop = 120
    If ActiveCell.Row > 65530 Then myTop = Cells(65530, ActiveCell.Column).Top
    If ActiveCell.Column > 250 Then myLeft = Cells(ActiveCell.Row, 250).Left
    With Selection
        .Left = myLeft
        .Top = myTop - 30
        .OnAction = "CellCoordinates"
    End With
    With Selection
        .Left = myLeft + 60
        .Top = myTop - 30
        .Text = "The active cell coordinates are " & ActiveCell.Left & _
            "," & ActiveCell.Top & "."
        .ShapeRange.Adjustments.Item(1) = 0
        .Autofit = True
        .OnAction = "CellCoordinates"
    End With
End Sub

You should end up something like this. (Turn on Gridlines again if you prefer)

A couple of things to note. I've added some If, Then code so the Assistant image won't hit the sides of the worksheet, (this had the effect of making him distorted). And I added OnAction to both the Assistant and Callout, this means you can make the macro work by clicking the shapes themselves.

Here's an example workbook to try. Make sure you enable macros for it to work. Have fun :-)

Update: Andy Pope MVP from AJP Excel Information very kindly showed me that it is possible to get rid of the buttons. Please refer to his comment. Thanks Andy!!!  
Posted by andrewe at 19:43

March 15, 2005

New Excel Forum

There's a new Excel Question and Answer forum in town...(well, maybe not so new, but the site itself is new)

As you can see, it's run by Colo aka Masaru Kaji (from Colo's Excel Junk Room), Gareth Lombard (from The Excel Maniacs) and starting from yesterday, myself also. Somehow I got chosen as the moderator for "Excel General Questions". (gulp)

Anyway, it's a forum that Colo and Gareth have run for the last couple of years, doing their best to help people solve problems of all levels, so please show them (us) your full support. Anyone can join, feel free to ask or answer any questions you like ;-)  
Posted by andrewe at 21:02

March 13, 2005

Office Assistant

To use or not to or not like the Office Assistant, this code is still of some interest, to me at least :-)

Sub Speak()
    With Assistant
        .Visible = True
        .On = True
    End With
    With Assistant.NewBalloon
        .Text = "Yes Sire?"
    End With
End Sub

Hopefully you'll get something like this,

Okay, my current record for tolerating the Office Assistant is about 5 minutes, this code will make he/she/it disappear until next time.

Sub GoAway()
    With Application.Assistant
        .AssistWithAlerts = True
        Select Case _
            .DoAlert("", _
                "Do you want me to go away?", _
                msoAlertButtonYesNo, _
                msoAlertIconNoIcon, _
                msoAlertDefaultFirst, _
    Case vbYes
      With Assistant
          .On = False
          .AssistWithAlerts = False
      End With
    End Select
  End With
End Sub

Kidding aside, the above code shows an example of using the Assistant to help with alerts, something you might like just for it's entertaiment value.

Here's a good link if you want some more information.  
Posted by andrewe at 19:19

March 10, 2005

New Custom Functions

Last night I decided to make some custom functions to work with Conditional Formatting. They've been added to Andrew's Custom Functions which you can download here.

1. GetColor has been replaced by GetColorIndex.
2. GetFontIndex has been added.
3. ColorName has been added.

Here's a picture of ColorName used to distinguish whether colors are Fill colors or Conditional Format colors. The "1" in Column F formulas refers to the 1st Conditional Format, if left out it refers to the Fill color. (Use "2" or "3" for the second or third Conditional Formats respectively).

In the case of cell B12, the Fill color and the first Conditional Format color are the same, something that might be missed if not checked carefully.

Just keep in mind that a) this function only works for the standard 56 colors and b) some color names are duplicated. Even so, it may come in handy depending on what you are using it for.  
Posted by andrewe at 19:51

March 06, 2005

Freeform Lines Look Cool 2

Here's some more things you can do with freeform lines.

In the below picture you can see two rectangles and two freeform lines. The freeform lines are identical, one was copied from the other.

Now I've used a Fill Color of Gray-40% and No Line for the Line Color of the two rectangles and the top freeform line.

Then I match the bottom (copy) freeform line color to the sheet color and turn off Gridlines (Tools, Options, View, Gridlines), using Order from the right click menu to bring this shape to the front. A little maneuvering and the result is...

Freefrom Lines and 3-D Style
This time I've taken a different approach. The shape below is one continuous freeform line, the corners were plotted by using the above-mentioned Gridlines as a reference. (Hold the mouse button down for jagged lines, release for straight lines, one click to start a new direction, two clicks to finish).

Next I start to trace along the jagged line to create the other half of the picture. (Just a rough copy is fine)

Now for some 3-D styling. I've selected Metal for the Surface, Gray-25% for the 3-D Color and Light Turquoise for the Fill Color. (Keep in mind that colors look darker in 3-D). The result looks quite realistic.

Here's something else. I've drawn another two freeform lines...

Made them 3-D... (yuck so far)

Then used Matte for the Surface, and changed the Line Colors (not the Fill Color) to get a nice ribbon effect.

Don't have quite the right shape? Use Edit Points from the right click menu to help.  
Posted by andrewe at 22:51

March 03, 2005

Freeform Lines Look Cool

Here's something I've started to use a lot recently... using Freeform lines to get that "hand-drawn" effect.

The Freeform button
You can access the Freeform button, from AutoShapes, Lines or you might find it worth your time to drag a button to a suitable toolbar if you plan on using it often. (Select Tools, Customize, Commands, AutoShapes and scroll down to Freeform)

Old and New Excel Versions
This is where it gets tricky. If you draw an enclosed shape using a Freeform line, the shape then becomes opaque and I seem to remember that the Transparency features of older Excel versions are not as good as the more recent versions. The best you can choose in this case is Semitransparent, which is grainy and quite hard to see through.

There is a simple solution. Draw an almost enclosed shape (use the white tags to adjust the size and/or increase the zoom size to make things easier). Right-click the shape and choose Close Path. You now have an totally enclosed shape that is perfectly transparent. (Or you could just double click an already opaque enclosed shape, then select No Fill for the Fill Color)

Let's try a few modifications by double-clicking the shape to bring up the Format AutoShape dialog box. As you can see, I'm lucky to have a newer Excel version and I've selected Turquoise as a Fill Color and set Transparency to 60 percent. At the same time, I've increased the Line Weight to 2.5 pt.

And here's how it looks.

Or you can select No Line for the Line Color and leave an interesting "stain" on your spreadsheet.

Leave a few of these on your files and people will start asking how you made them (at least they did where I work ;-) ) Don't forget you also have the option to print them or not. Look at the bottom of this post.  
Posted by andrewe at 23:17