April 27, 2006

Spoke too soon

Had a spare hour at work, added another option to bookmark Folders.



Once set, you can use this kind of bookmark to show the Open Dialog box in that particular directory. A big timesaver.

And pushing the far left button now displays the current version date. (Thanks Sige)

Here's the link.

Time to pack...see you in the second week of May. Stay safe ;-)  

Posted by andrewe at 20:32

April 25, 2006

New Book Navigator 2

Okay, this is what? The New, New, New Book Navigator? But this really is a lot better, trust me :-)



As you can see, I have added a drop down list and you can add, delete, overwrite bookmarks as you like. Use any names you like, you can navigate from book to book and even open them if they are closed.

The sheet scrolling buttons are improved too, you can scroll to the next visible sheet, hidden sheets are now ignored.

Finally there are some new buttons to help you scroll up, down left and right.

Have to admit, I really enjoyed making this. Hope you like it too.

Here is the download link.  
Posted by andrewe at 21:46

April 20, 2006

New Rounded Comments Form

Last time I mentioned I might add some more options to the Rounded Comments form. Well I did. And it's a lot better than before (at least in my opinion)

There are now 3 different types of comments that you can use, Normal, Values and Formulas. All 3 types can be entered into multiple cells at the same time.



"Normal" comments are added with whatever is displayed in the text box. So if you want the same content in all selected comments, this is the way to do it. "Normal" is also the default setting when you use Rounded Comments for the first time.



"Values" comments are added with whatever text is displayed in each selected cell. So if you have a set of cells with some numbers, the comments added will contain the number from each of those cells, formatted to show the same way as the cells.



"Formulas" comments are added with whatever formulas exist in each selected cell. This is a good way to store formulas if you doing some editing but want to refer to them later.



You will also notice a button that says "Rounded". This too is a default setting. When Rounded Comment mode is enabled, all comments show the rounded shape and are autosized. But good news for those who want the other features but still use the standard Excel comments, just push the button to disenable Rounded Comment mode.



Finally we have colors. I thought it would be a good idea to have them handy - on the form itself. The default color is Ivory, the same color as standard comments, pushing the color buttons changes the color and stores the setting between sessions. All of the above settings are retained too (as is the "Show comments when finished" option)

In the past few days, I've found it very useful and convenient. I hope you do too.

The download link remains the same, you can get it here.  
Posted by andrewe at 19:35

April 12, 2006

Menu Item Positioning - Rounded Comments

Yesterday I decided to add a new item to the right click menu. (By "right click menu", I am referring to the "standard" Cell menu, but not just the one you see normally when cells are selected and you right click, also the Print Preview one and XML one, three in total as far as I can tell)

Anyway, to set the position, we have to know where to place it. It's not just a matter of counting from the top - other code may add menu items and also the right click menu changes according to the cells selected. Have a look while selecting cells with and without comments to see what I mean. Menu items appear and disappear accordingly.

Using Control IDs are a way to get around this. I prefer this to using Captions, the main reason is I use both English and Japanese Excel, the Captions change depending on which computer I am using (work or home) or which language settings I am using. In this respect Control IDs are a lot more "International Friendly".

Here's some code to get the IDs.

Sub ShowMenuDetails()
    On Error Resume Next
    Dim cBar As CommandBar
    Dim ctrl As CommandBarControl
    Dim i As Long, AppCalc As Integer
    AppCalc = Application.Calculation
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    ActiveWorkbook.Sheets.Add.Name = "Menu Details"
    Range("A1").Value = "Menu Name"
    Range("B1").Value = "Caption"
    Range("C1").Value = "ID No."
    Range("D1").Value = "Visible"
    Range("E1").Value = "Menu Type"
    Range("F1").Value = "(Local Name)"
    Range("A1:F1").Font.Bold = True
    i = 2
    For Each cBar In Application.CommandBars
        If cBar.BuiltIn Then
            Cells(i, 1) = cBar.Name
            Cells(i, 4) = cBar.Visible
            Cells(i, 6) = cBar.NameLocal
            Select Case cBar.Type
            Case msoBarTypeMenuBar: Cells(i, 5) = "Menu Bar"
            Case msoBarTypeNormal: Cells(i, 5) = "Normal"
            Case msoBarTypePopup: Cells(i, 5) = "Popup"
            End Select
            For Each ctrl In cBar.Controls
                If ctrl.BuiltIn Then
                    Cells(i, 2) = ctrl.Caption
                    Cells(i, 3) = ctrl.ID
                    i = i + 1
                End If
            Next
            i = i + 1
        End If
    Next
    Cells(1, 1).Select
    Columns.AutoFit
    Columns("C:C").HorizontalAlignment = xlCenter
    Application.Calculation = AppCalc
    Application.ScreenUpdating = True
    On Error GoTo 0
End Sub

Now the menu item I want to add is Rounded Comments. They are included with my Utilities, but I thought it would be convenient to have them as a right click menu item also. Where I want to add the menu item is just above Format Cells so I look for the ID which is 855.

Sub AddCellMenu()
    On Error Resume Next
    Dim cPos As Integer
    Dim cBar As CommandBar
    Dim ctrl As CommandBarControl

    'Remove any previous menu items
    ResetCellMenu

    ' Refer to all "Cell" command bars
    For Each cBar In Application.CommandBars
        With cBar
            If .Controls.Parent.Name = "Cell" Then

                ' Get the Menu Item position from "Format Cells"
                cPos = .FindControl(ID:=855).Index

                ' Add the "Rounded Comments" menu item as below
                With .Controls.Add(Type:=msoControlButton, Before:=cPos)
                    .Caption = "&Rounded Comments"
                    .FaceId = 1589 ' change button image here
                    .OnAction = "LaunchRoundedCommentsForm"
                End With

            End If
        End With
    Next
    On Error GoTo 0
End Sub



Before adding a menu item we should be sure that previous cases are removed to avoid duplicates (Okay, I've been caught out before but this my "safe" code, a few extra nanoseconds won't hurt anyone). This is why ResetCellMenu is run with the above code before the new item is added. If a previous item doesn't exist, no problem, better safe than sorry. I'm using Captions in this case because I can be sure that mine won't change according to the language used, even if other menu items do.

Sub ResetCellMenu()
    On Error Resume Next
    Dim cBar As CommandBar
    Dim ctrl As CommandBarControl
    For Each cBar In Application.CommandBars
        For Each ctrl In cBar.Controls

            ' Find the "Rounded Comments" menu item and delete
            If ctrl.Caption = "&Rounded Comments" Then ctrl.Delete

        Next ctrl
    Next cBar
    On Error GoTo 0
End Sub

Finally, the code to be run. This launches the Rounded Comments form. (Thanks Andy and Jon for setting me straight on that)

Note "vbModeless" is being used. For Excel versions 2000 and up, this means you can select ranges while the user form is showing. This really is very convenient so I usually try to use it depending on what I actually want from the user form.

Sub LaunchRoundedCommentsForm()
    On Error Resume Next
    #If VBA6 Then
        UserFormRComments.Show vbModeless
    #Else
        UserFormRComments.Show
    #End If
    On Error GoTo 0
End Sub

Here's an addin that shows the above code and will enable you to add rounded comments. You can add the same comment to multiple cells at the same time, it is also a lot easier to edit them. (I might add some more options in future also)



The download link is here.

Update: New features have been added to the Rounded Comments form. Please refer to the next post.  
Posted by andrewe at 19:01

April 05, 2006

Excel Dialog Box Example - Find By Color

Earlier Excel versions don't always have some of the features found in later versions. Well, this much should be obvious, the trick is making a workaround to do the same thing.

Let's consider finding cells with a specified color. Rather than use a userform with a color pallete, how about using an Excel color pallet that already exists?

In this case, the Patterns dialog box. It's the same one used with Format Cells on the Right Click menu.

This code shows the dialog box itself.

Application.Dialogs(xlDialogPatterns).Show



Once we know how to do that, we can use it to color something, in this case the active cell of a selected range and then loop through each one of these cells and select those that have the same color. Two things to keep in mind are these.

1. The orginal color of the Active Cell may be the same color as we wish to look for. This means it should be included in the loop, otherwise it should be disregarded.

2. Regardless whether the orginal color is the one we want, if we set it back to the same color after the code has run, we can be sure that all of the cell colors were the same as they were before.

Here's the code. Try to spot where the above points are included. (Make sure your range is selected first)

Sub FindByColor()
    On Error Resume Next
    If TypeName(Selection) <> "Range" Then Exit Sub
    Dim aColor As Long, rColor As Long
    Dim c As Range, aCell As Range
    Dim myRange As Range, colorRange As Range
    Set aCell = ActiveCell
    Set myRange = Selection
    Application.ScreenUpdating = False
    aCell.Select
    aColor = aCell.Interior.ColorIndex
    Application.Dialogs(xlDialogPatterns).Show
    rColor = aCell.Interior.ColorIndex
    If aColor = rColor Then
        For Each c In myRange
            If c.Interior.ColorIndex = rColor Then
                If colorRange Is Nothing Then
                    Set colorRange = c
                Else
                    Set colorRange = Union(colorRange, c)
                End If
            End If
        Next
    Else
        For Each c In myRange
            If c.Interior.ColorIndex = rColor _
               And c.Address <> aCell.Address Then
                If colorRange Is Nothing Then
                    Set colorRange = c
                Else
                    Set colorRange = Union(colorRange, c)
                End If
            End If
        Next
    End If
    colorRange.Select
    aCell.Interior.ColorIndex = aColor
    Application.ScreenUpdating = True
    ' Clean up after the code has run
    Set aCell = Nothing
    Set myRange = Nothing
    Set colorRange = Nothing
    On Error GoTo 0
End Sub

Of course, we can expand on this to do all kinds of things if the right type of dialog box is available. Using Excel to work with Excel, waste not, want not ;-)  
Posted by andrewe at 00:07