December 27, 2004

Toggles

Toggling things on and off using VBA is reasonably straightforward.

Here is a way to toggle those dreaded Gridlines (You can also go to Tools, Options, View and untick the checkbox, or you could use the Toggle Grid button the Forms Toolbar. Right click any Toolbar or select View, Toolbars, Forms) Actually, Gridlines come in very handy when aligning Autoshapes so I shouldn't malign them too much :-)

Sub ToggleGridlines()
    ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines
End Sub

This is okay for a simple True or False selection (On or Off). Here is a way to allow either restricted access to Range A1:J10 or full access Worksheet using ScrollArea.

Sub ToggleScrollArea()
    If Not ActiveSheet.ScrollArea = ("") Then ActiveSheet.ScrollArea = ("") _
        Else ActiveSheet.ScrollArea = "A1:J10"
End Sub

This is the second time I've mentioned ScrollArea. I noticed a few interesting things when I wrote the above macro, I might take a closer look in future.  

Posted by andrewe at 22:46

December 25, 2004

Peace on Earth

No matter where you come from, I wish you all a very



and a very Happy New Year for 2005. Drive safely and have a great time.
  
Posted by andrewe at 00:37

December 20, 2004

Year Adder

It sounds like some kind of a snake but I assure you it's not. If you find yourself continuously adding or subtracting a year or two to dates around New Year, this might come in handy. (Thanks to Dianne for her advice to use DateAdd)

Year Adder

Sub AddYears()
    Application.ScreenUpdating = False
    On Error GoTo ErrorHandler
    Dim myYears As Integer
    Dim TargetCell As Range
    myYears = InputBox("Enter Number of Years" _
        & vbCrLf & "(Use a minus sign in front to subtract)", _
        "Andrew's Handy Dandy Year Adder")
    For Each TargetCell In Selection
        TargetCell.Value = DateAdd("yyyy", myYears, TargetCell)
    Next
    Application.ScreenUpdating = True
ErrorHandler: Exit Sub
End Sub

Here's what it looks like in action (Yeah, I thought the name sounds corny too)



Voila!



Just be forewarned that it will format blank cells as dates too. Use with caution ;-)  
Posted by andrewe at 21:39

December 18, 2004

Date Formatting

I've mentioned before that one of my many jobs is managing Shipping, (I really hate working for a living) so I find myself sending stuff to different countries around the world. And some of these countries use different formats for dates.

Here are some macros I made to make life a little easier. Rather than going through the process of formatting manually, I thought these might save a bit of time (especially if you do it every day).

Format as Day/Month/Year
Sub DateFormatDMY()
    Dim TargetCell As Range
    For Each TargetCell In Selection
        TargetCell.NumberFormat = "dd/mm/yy"
    Next TargetCell
End Sub

Format As Month/Day/Year
Sub DateFormatMDY()
    Dim TargetCell As Range
    For Each TargetCell In Selection
        TargetCell.NumberFormat = "mm/dd/yy"
    Next TargetCell
End Sub

DateFormat Function
Another way I thought of is to format dates by using a Custom Function. In this case I can change exisiting dates by choosing it's format by selecting a number such as 1 for Day/Month/Year, 2 for Month/Day/Year etc. What this really means is that I could just use a single cell to change all of the dates throughout a WorkBook with this function automatically. Now that's gotta save lots and lots of time.

Function DateFormat(TargetCell As Range, Optional ChooseFormat As Long)
    Select Case ChooseFormat
    Case IsMissing(ChooseFormat)
        DateFormat = Format(TargetCell, TargetCell.NumberFormatLocal)
    Case 1
        DateFormat = Format(TargetCell, "dd/mm/yy")
    Case 2
        DateFormat = Format(TargetCell, "mm/dd/yy")
    Case 3
        DateFormat = Format(TargetCell, "d mmmm, yyyy")
    Case 4
        DateFormat = Format(TargetCell, "mmmm d, yyyy")
    End Select
End Function

Here is what it looks like.



As you can see, the ChooseFormat is optional. If you decline to use it or use a 0, formatting will remain the same as the existing date.

Note: This could also be done by using regular functions such as CHOOSE and TEXT like this.

=CHOOSE(1,TEXT(B2,"dd/mm/yy"),TEXT(B2,"mm/dd/yy"),TEXT(B2,"d mmmm, yyyy"),TEXT(B2,"m dddd, yyyy"))

However, you may have to change your Local Settings on the Control Panel to get the results you want.  
Posted by andrewe at 22:55

December 15, 2004

See Through Shapes

Ever want to show part of a shape as "see through"? Look at this picture to see what I mean.



It's a good effect so here is a way to do it.

First make the shape you want to appear "behind". Then make the shape you want to appear "in front". Go back to the first shape, select it and make a copy.

The next part is rather sneaky. Change the Line Style of the copy to 1/4 pt (the thinnest available) and the change Dash Style to "Dash". Then change the Fill Color to No Fill.



Go back the first and second shapes and change the Line Style to about 1 1/2 pt (5th down) and also change the Line Color to Gray - 80%. This is match the copy as it seems to become lighter when the Line Style is changed.

Place the shape with the dashed line directly above it's original shape. (If necessary, use the Order buttons on the right-click menu to make it appear in the right position)

You should now have something like the top picture. Try moving the "in front" shape around. The dashed line shape should "change" to match.



Magic?



You can also get 3-D Style, (Style 4) for transparent shapes but not with dashed lines. So how did I get the shape on the left?



2 squares and 2 parallelograms, with dashed lines and no Fill Color. Who says a cube needs to be made up of 6 sides? ;-)  
Posted by andrewe at 22:12

December 12, 2004

Names vs. Numbers

Have you found yourself in this situation? Rather than type full names, you decide to use to start off using reference numbers, but after a while there are so many numbers that it becomes impossible to remember them all.



This might be a solution. Make a list of the names using Data Validation as below.



This should give you a pop up list that appears each time you select the cell. (Copy and paste as required)



Now you have the names but if you still need the numbers, perhaps you can get the number by using the MATCH function.

=IF(E2="","",MATCH(E2,$C$13:$C$22,0)+1233)



In this case, MATCH returns the position that name shown in cell E2 appears in the range C13:C22. ("0" is the same as False, both will look for an exact match)

I've also added IF to return nothing if cell E2 is blank and since the start number is 1234, just adjusted the MATCH part of the formula to suit.

Note: If MATCH doesn't work because of the way your numbers are set up, consider using VLOOKUP or SUMPRODUCT instead.  
Posted by andrewe at 22:27

December 08, 2004

GetComment and GetFont

Here are some more Custom Functions that might come in handy.

GetComment
This will "extract" what is written in comments. It can even refer to a comment in the same cell without causing problems with circular references.

Function GetComment(Cell As Range) As String
    GetComment = Cell.Comment.Text
End Function



GetFont
Sometimes cells end up with different font sizes or styles. Since this can be hard to spot, I thought this function would work well with Conditional Formatting. The font in Cell C4 is Arial 9, the rest of the selection is Arial 11.

Function GetFont(Cell As Range) As String
    GetFont = Cell.Font.Name & " " & Cell.Font.Size
End Function



Here is a collection of Custom Functions used so far. You can import them into your Visual Basic Editor so they can be used in your own files.  
Posted by andrewe at 23:44

December 05, 2004

VBA Express



If you are interested in studying VBA, you might like to know that the VBA Express site is offering VBA Excel Training and Certification. It's a comprehensive course for beginners (like myself), and the certification at the end is good inspiration to give it your best.

You'll find the folk at the forum very friendly and supportive, and the site itself is great place to learn and have some fun.

Don't forget to challenge Dreamboat to a game of Tetris, but beware - she's had lots of practice ;-)  
Posted by andrewe at 20:08

Text and Shapes

One thing about shapes in Excel is the difficulty of aligning text.

For example, here we have a picture of text being formatted inside an shape.



It looks fine until the shape is rotated like this.



How to overcome? There are 2 ways that I like. If you want the text at the same angle as the shape, use WordArt like below to get the desired results.



If you don't have so much text, you may prefer not to rotate it, just the shape itself. Even so, depending on the type of shape that you use, it can be difficult to get it centered exactly.



In this case, my suggestion is to use another shape, but formatted with No Fill and No Line.



This should enable you to position it with a minimum of fuss.  
Posted by andrewe at 19:57

December 01, 2004

Color Picker

You've probably seen them before, those little eye-droppers called Color Pickers you use to find color properties that look like this.



Anyway, since everyone and his/her pet dog seem to be making them I thought it would interesting and useful to try it myself. Rather than refer to an entire list, I just wanted to know the properties of a particular cell, so I decided that a simple message box would be fine.

Here's some code I used to find the Color Index.



Sub ColorPicker()

    Dim Msg As String

    Msg = ActiveCell.Interior.ColorIndex
    Msg = "The Color Index is " & Msg

    MsgBox Msg

    Calculate

End Sub

Not a masterpiece but easy enough to understand.

Then I thought it would be nice to know the HTML Color too. So I added some "borrowed" code to get this.



Sub ColorPicker2()

    Dim myColor As String
    Dim HTMLcolor As String
    Dim Msg As String
    myColor = Right("000000" & Hex(ActiveCell.Interior.Color), 6)
    HTMLcolor = "#" & Right(myColor, 2) & Mid(myColor, 3, 2) & Left(myColor, 2)

    Msg = ActiveCell.Interior.ColorIndex
    Msg = "The Color Index is " & Msg
    Msg = Msg & vbCrLf & vbCrLf & "The HTML color is " & HTMLcolor

    MsgBox Msg

    Calculate

End Sub

So far my computer has not melted so it seems to work okay. And now...back to more study ;-)  
Posted by andrewe at 23:19