How time flies when you are having fun. I'm in the middle of my first real vacation since I came back to Australia and it was certainly worth the wait.

Anyway, this year has been a good year for me but it can always be improved. I've made my New Year's resolution for this blog in 2009 but it's still a secret. With a bit of hard work, maybe it will come true.

See you in the new year :-)

The following code might be useful. You can use it to insert rows, lines or pagebreaks where values change in a column by changing one line of code.

Sub InsertRowsAtColumnDifferences()
    Dim lStartRow As Long
    Dim lLastRow As Long
    Dim lCounter As Long

    On Error Resume Next

    If ActiveWorkbook Is Nothing Then Exit Sub

    If TypeName(Selection) <> "Range" Then Exit Sub

    With Selection

        lStartRow = .Row

        lLastRow = lStartRow + .Columns(1).Cells.Count - 1

        For lCounter = lLastRow To lStartRow Step -1

            If Cells(lCounter, .Column).Value <> Cells(lCounter, .Column).Offset(1).Value Then

                If Cells(lCounter, .Column).Value <> "" And Cells(lCounter, .Column).Offset(1).Value <> "" Then

                    Cells(lCounter, .Column).Offset(1).EntireRow.Insert 'insert rows
                    
                    '.Rows(lCounter - lStartRow + 1).Borders(xlEdgeBottom).LineStyle = xlContinuous 'add lines
                    
                    'ActiveSheet.HPageBreaks.Add Before:=Cells(lCounter, .Column).Offset(1) 'add pagebreaks

                End If

            End If

        Next lCounter

    End With

    On Error GoTo 0
End Sub
I will be adding and other code this to my utilities in the near future. But I would like to hear from your ideas too.

What new tools would you like to see? Are there any repetitous or tiresome tasks you perform that could be made eaiser? (Housework and taking out the garbage not included!)

Send me an email (look on the left of this blog for the address). If you can suggest something that might be useful for a large number of Excel users, I want to hear from you :-)

When Excel 2007 came out, the old style menus were replaced with the Ribbon.

Actually no, this isn't what really happened.The right click menus are still there (always have been) and the menus and toobars that were formerly at the top were just hidden. But they are still there, and you can unhide them quite easily. (Note that a few menu items such as File Search are disabled for various reasons, although most of them will still work fine) This code will show the top menu and selected items from the Standard and Formatting toolbars on the Add-Ins tab.

Private Sub ShowOldStyleMenus()
    On Error Resume Next
    Dim cBar As CommandBar
    Dim cBarCtrl As CommandBarControl
    Dim sMenuName As String
    Dim sToolbarName As String
    Dim iMenu As Integer

    sMenuName = "Old Style Menu"
    sToolbarName = "Old StyleToolbar"

    CommandBars(sMenuName).Delete

    Set cBar = CommandBars.Add(sMenuName, , , True)

    With cBar

        .Visible = True

        For iMenu = 1 To 10

            Set cBarCtrl = .Controls.Add(Type:=msoControlPopup, ID:=30001 + iMenu)

        Next iMenu

        Set cBarCtrl = .Controls.Add(Type:=msoControlPopup, ID:=30022) 'Chart
        Set cBarCtrl = .Controls.Add(Type:=msoControlPopup, ID:=30177) 'AutoShapes

    End With

    CommandBars(sToolbarName).Delete

    Set cBar = CommandBars.Add(sToolbarName, , , True)

    With cBar

        .Visible = True

        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=2520) 'New
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=23) 'Open
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=3) 'Save
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=4) 'Print
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=109) 'Print Preview
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=2) 'Spelling
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=21) 'Cut
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=19) 'Copy
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=22) 'Paste
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=108) 'Format Painter
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=210) 'Sort Ascending
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=211) 'Sort Descending
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=984) 'Help
        Set cBarCtrl = .Controls.Add(Type:=msoControlComboBox, ID:=1728) 'Font
        Set cBarCtrl = .Controls.Add(Type:=msoControlComboBox, ID:=1731) 'Font Size
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=113) 'Bold
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=114) 'Italic
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=115) 'Underline
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=120) 'Align Left
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=122) 'Center
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=121) 'Align Right
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=402) 'Merge and Center
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=395) 'Accounting Number Format
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=396) 'Percent Style
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=397) 'Comma Style
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=398) 'Increase Decimal
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=399) 'Decrease Decimal
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=3162) 'Decrease Indent
        Set cBarCtrl = .Controls.Add(Type:=msoControlButton, ID:=3161) 'Increase Indent

    End With

    Set cBar = Nothing
    Set cBarCtrl = Nothing

    On Error GoTo 0
End Sub

Here's how it looks.


Keep in mind it's also possible to do this with XML and do it better, I've seen it done with both freeware and shareware downloads (very impressive) and have also experimented with it myself. But the advantage of doing it with VBA like above is that you can just copy and paste it somewhere like your Personal workbook, or you can email it to someone in a hurry. That said, if you do have 2007, you will probably be better off learning where everything is in the long run :-)

I've added the above code to the 2007 version of JMT Excel Utilities (Show Old Style Menus in the the Developer Tools tab). The download link is here.

This is a file I thought might come in useful at work. Here it is when you open it.



After entering a suitable title in cell D2, start to enter items starting from (and including) cell D4 downwards. When you that the item number appears in column B and a drop down (data validation) list appears in column C. The default value is X (unchecked). If you choose O (checked), the item row cells, (columns B,C and D) change color as an easy means of identification.



You can have spaces between the rows. In the following image, I have widened the size of column A and added some dummy headers. Even with spaces, the code will produce the correct item number in column B.



After your checklist is ready, you can copy it to another workbook using Excel's built-in commands on the sheet tab right click menu. Then you can choose to delete the checklist code or comment it out. The latter might be better if you might need to make changes in future.

Download it here :-)