For many people out there, who have written AddIns for Excel using VB6, they find themselves with a dilemma: how do I add my custom toolbar to another Ribbon tab?

Well, I honestly do not have an answer to that question; however, we can still customize the Ribbon and move this Custom Toolbar to another location. To be specific, we can move it to the QAT (Quick Access Toolbar).

First off, you need to add your custom toolbar. You can use VBA to add this custom toolbar as follows:

Sub test()
    Dim cmdBar As CommandBar
    Dim btn As CommandBarButton

    On Error Resume Next
    Application.CommandBars("Testing").Delete()
    cmdBar = Application.CommandBars.Add
    cmdBar.Name = "Testing"
    cmdBar.Visible = True

    btn = cmdBar.Controls.Add(Type:=msoControlButton)

    With btn
        .Style = msoButtonIconAndCaption
        .FaceId = 986
        .Caption = "Robert Martim"
    End With
End Sub

The above code will add the following to the Add-Ins tab:

Now, you create your other Excel document where you wish to add the GroupAddInsCustomToolbars to it. Open this Excel document using the custom UI Editor where the following XML must be inserted:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"

      xmlns:mso="http://schemas.microsoft.com/office/2006/01/customui">

      <ribbon startFromScratch="true">

       <qat>

        <documentControls>

         <mso:control idQ="mso:GroupAddInsCustomToolbars" visible="true"/>

        </documentControls>

       </qat>

      </ribbon>

</customUI>

The GroupAddInsCustomToolbars is placed under the mso namespace; therefore we must refer to it and then get the idQ for it. Remember that GroupAddInsCustomToolbars is not a group per se, but a control (it is a commandbar control). Hence, if you try to place it under a tab, it fails since it cannot find the group.

Also, remember that to customize the QAT you must start from scratch. The final result is:

In a video (http://www.youtube.com/watch?v=bGjqDGF7xaM) published on YouTube back in 2008, I explained how to highlight the active row in Excel. In this brief article, I discuss how you can highlight the active cell in Excel. In this particular case, I will only change the font color of the cell, but you can later change the conditional formatting and apply whatever formatting you wish.

Here's how it is done:

1. Create a named range called AddressOfActiveCell using the formula: =ADDRESS(5,6)
2. Select the entire worksheet and apply a conditional formatting using the formula (the active cell being A1 upon selection): =ADDRESS(ROW(A1),COLUMN(A1))=AddressOfActiveCell
3. Now, open the visual basic editor and insert the following code:


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    With ThisWorkbook.Names("AddressOfActiveCell")
        .Name = "AddressOfActiveCell"
        .RefersToR1C1 = "=address(" & ActiveCell.Row & "," & ActiveCell.Column & ")"
    End With

End Sub

Check the video tutorial here:

VERSION: 14.0.4006.1110

Excel 2010 now comes with sparklines. In the past, we needed third party software in order to work with these little creatures. They are now native to Excel. Here’s a look at the Excel 2010 sparklines:

Figure 1: Line Sparkline

In the above scenario, we have a line sparkline, but Excel 2010 comes with some others such as “column sparkline” and “win/loss sparkline” shown below for the same dataset:

Figure 2: Column Sparkline with high points (light color)

If we consider the conditional formatting data bars introduced in Excel 2007, this is a great addition to Excel 2010.

Most importantly: it is very easy to use. Under the “Insert” tab, close to the “Charts” group you will find the “Sparklines” group. The shaded area in the picture below shows the sparkline location:

Figure 3: Sparkline group under the Insert tab

Once the sparkline is inserted, you can quickly change style, type, show/hide low/high points, etc:

Figure 4: Sparkline extensibility tab and Sparkline Design tab

VERSION: 14.0.4006.1110

I have officially gotten hold of Microsoft Office 2010 Technical Preview and have it now running in a virtual machine for testing. The first thing we notice is that the Office Button has given place to the "Office Tab". Well, the name officially remains as "Office Button", but it does not look like a button to me. It is really a tab, but there you go:

Figure 1 & 2: Office Button (version 12) and Office Tab (version 14)

The cool thing, however, is when you click on this "Office Tab". Now, we have a nice-looking screen where you can choose options from. The feel is similar to the "welcome screen" of Access 2007. I think this is a much cleaner way to expose features than in Microsoft Office 2007:


Figure 3: Office Tab screen

When you click on "Print" for example, instead of the usual Print Dialog Box, you simply get all the necessary stuff on a similar screen. Sweet! However, the thing I did not like was the "back" button. I think it is unnecessary and confusing. It would be better to simply keep the other tabs visible and the user can simply click on a tab and move away from the Office Tab and go about her merry way. I hope Microsoft will realize this is a hindrance and keep all tabs visible.

The contents of the tabs remain pretty much the same except for the "Insert" tab. I will be looking at it tomorrow and posting some of the new stuff. It is time to hit the hay!