web 2.0


Excel 2007 Add-in: Ordering worksheet tabs and viewing cell details

In this short article, we willlook at the creating of an Add-in for Excel 2007 using VSTO 2008. In this ExcelAdd-in, we will create:

1.
    A Commandbarbutton to be added to Excel’s Ply menu which will be used to order the sheettabs;
2.     A Commandbar button to beadded to Excel’s Cell popup menu which will be used to show details about theactive cell.


Start by creating a new Excel Add-in project as follows:

1.
    File à New Project
2.
    Project Types à Office 2007
3.
    Excel 2007 Add-in


Picture 1: Creating a new VSTO 2008 Excel Add-in project

With the projectcreated, we will add the following code to it:

Public Class ThisAddIn
    'Events tocontrol the commandbar button click event
    PrivateWithEvents oBtnHandleOrderTabs As Office.CommandBarButton
    PrivateWithEvents oBtnHandleCellDetails As Office.CommandBarButton

    'Commandbarcontrols used in the code
    PrivateoBtn As Office.CommandBarButton
    DimoCmdBarPopup As Office.CommandBar
    DimoEditCtl As Office.CommandBarControl

    'Stringsused in this code
    PrivateConst strconPOPUP_NAME AsString = "INFO_POPUP"
    PrivateConst strconBtn_POPUP_CAPTION As String = "ABOUT THE ACTIVE CELL"
    PrivateConst strconCmdBar_Cell_NAME As String = "Cell"
    PrivateConst strconCmdBar_Ply_NAME As String = "Ply"
    PrivateConst strconBtn_Ply_CAPTION As String = "Order Tabs"
    PrivateConst strconBtn_Cell_CAPTION As String = "View Cell Details"

    PrivateSub ThisAddIn_Startup(ByValsender As Object,_
                                  ByVal e AsSystem.EventArgs) Handles Me.Startup

        Try
            'Uponstarting up this Add-in, try:
            'Controlsare temporary; hence delete/reset is just a "fail-safe"
           Application.CommandBars(strconPOPUP_NAME).Delete()
           Application.CommandBars(strconCmdBar_Cell_NAME).Reset()
           Application.CommandBars(strconCmdBar_Ply_NAME).Reset()
        Catch ex As Exception
            'Catchexception and show to user
            MsgBox(ex.Message)
        EndTry
        'Callthe sub-routine to create menu items
        CallCreateMenu()
    End Sub

    PrivateSub ThisAddIn_Shutdown(ByValsender As Object,_
                                   ByVal e AsSystem.EventArgs) Handles Me.Shutdown
        'Nocode added here
    End Sub

    SubCreateMenu()
        'Author         : Robert Martim
        'Purpose        : Create menu items
        'Createdon     : 18 April 2009
        'Lastupdated   : 18 April 2009       

        'Add atemporary button to the Ply menu
        oBtn =Application.CommandBars(strconCmdBar_Ply_NAME) _
           .Controls.Add(Office.MsoControlType.msoControlButton, , , 1, True)

        'Definea few properties for the button
        WithoBtn
            .Caption =strconBtn_Ply_CAPTION

            .FaceId =210

            .Style =Office.MsoButtonStyle.msoButtonIconAndCaption
        EndWith

        'Setthe button so that its click event can be controlled
        oBtnHandleOrderTabs = oBtn

        'Add atemporary button to the Cell menu
        oBtn =Application.CommandBars(strconCmdBar_Cell_NAME) _
         .Controls.Add(Office.MsoControlType.msoControlButton, , , 1, True)

        'Definea few properties for the button
        WithoBtn
            .Caption =strconBtn_Cell_CAPTION
            .FaceId = 326
            .Style =Office.MsoButtonStyle.msoButtonIconAndCaption
        EndWith

        'Setthe button so that its click event can be controlled
        oBtnHandleCellDetails =oBtn

        '************************************************************
        'Adda popup menu to the commandbars collection
        oCmdBarPopup = Application.CommandBars.Add(_
          Name:=strconPOPUP_NAME,Position:=Office.MsoBarPosition.msoBarPopup)

        'Adda fixed button to work as a header label of the popup menu
        oBtn =oCmdBarPopup.Controls.Add( _
          Type:=Office.MsoControlType.msoControlButton)
        WithoBtn
            .Caption =strconBtn_POPUP_CAPTION
            .Width = 40
            .Enabled = False
        EndWith

        'Populatethe popup with 4 Edit controls
        ForI = 1 To 4
            oEditCtl = oCmdBarPopup.Controls.Add(_
             Type:=Office.MsoControlType.msoControlEdit)
            oEditCtl.Width = 200
        NextI

        'Setsome properties for the popup
        WithoCmdBarPopup
            .Width = 200
            .Protection =Microsoft.Office.Core.MsoBarProtection.msoBarNoChangeDock + _
             Microsoft.Office.Core.MsoBarProtection.msoBarNoCustomize + _
             Microsoft.Office.Core.MsoBarProtection.msoBarNoResize
        EndWith

    EndSub

    SubInfo()
        'Author         : Robert Martim
        'Purpose        : Sub to fill Edit control with celldetails
        'Createdon     : 18 April 2009
        'Lastupdated   : 18 April 2009  

        DimoActiveCell As Excel.Range

        Try
            'Try:
            'to fill Edit control with cell details
            oActiveCell =Application.ActiveCell
            WithApplication.CommandBars(strconPOPUP_NAME).Controls
                If oActiveCell.Formula = ""Then
                    .Item(2).Text= "This cell has no formula"
                ElseIf oActiveCell.Locked = TrueThen
                    .Item(2).Text= "Formula: " &oActiveCell.Formula
                End If
                .Item(3).Text = "Format: " & oActiveCell.NumberFormat
                .Item(4).Text = "Font name: " &oActiveCell.Font.Name
                .Item(5).Text = "Total of CFs on active cell: " & _
                   oActiveCell.FormatConditions.Count
            End With
        Catchex As Exception
            'Catch exception and show to user
            MsgBox(ex.Message)
        EndTry
        oActiveCell = Nothing
    EndSub


    SubOrderTabs()
        'Author         : Robert Martim
        'Purpose        : Order active workbook sheets
        'Createdon     : 18 April 2009
        'Lastupdated   : 18 April 2009  

        DimoWorkbook As Excel.Workbook
        DimiCount1 As Integer
        DimiCount2 As Integer

        Try
            oWorkbook =Application.ActiveWorkbook

            For iCount1 = 2 TooWorkbook.Sheets.Count Step 1
                For iCount2 = 1 ToiCount1 Step 1
                    If oWorkbook.Sheets(iCount2).Name >oWorkbook.Sheets(iCount1).Name Then
                       oWorkbook.Sheets(iCount1).Move(Before:=oWorkbook.Sheets(iCount2))
                    End If
                Next iCount2
            Next iCount1

        Catchex As Exception
            '     MsgBox(ex.Message)
        EndTry

        oWorkbook = Nothing

    End Sub

    Private SuboBtnHandleOrderTabs_Click(ByVal Ctrl As  _
                                         Microsoft.Office.Core.CommandBarButton, _
                                         ByRef CancelDefault As Boolean) _
                                         Handles oBtnHandleOrderTabs.Click
        'Author         : Robert Martim
        'Purpose        : Handles click event on the"Order Tabs" button
        'Createdon     : 18 April 2009
        'Lastupdated   : 18 April 2009 

        CallOrderTabs()
    End Sub

    PrivateSub oBtnHandleCellDetails_Click(ByVal Ctrl _
                                           AsMicrosoft.Office.Core.CommandBarButton, _
                                           ByRef CancelDefault As Boolean) _
                                           Handles oBtnHandleCellDetails.Click
        'Author         : Robert Martim
        'Purpose        : Handles click event on the "CellDetails" button
        'Createdon     : 18 April 2009
        'Lastupdated   : 18 April 2009  
        CallInfo()
       Application.CommandBars(strconPOPUP_NAME).ShowPopup()

    End Sub
End Class


Upon right-clicking on any Excel worksheet, the Ply popup menu will be shown,only this time it will have an extra Commandbar button added to it. You can nowclick on this Commandbar button to order the tabs alphabetically:
 

Picture 2: Order Tabs commandbar button

As for the second Commandbarbutton, it will be shown when we right-click on any cell of the activeworksheet:
 

Picture 3: View Cell Details commandbar button
Finally, when we click onthis button our custom Commandbar popup is shown along with the cell’sinformation as viewed in the picture below: 
Picture 4: Cell Details commandbar popup menu

Tags: , , , ,

Microsoft Excel | VSTO - Excel

Comments are closed