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

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

A common problem for Outlookusers is handling incoming attachments, especially when they are a norm not theexception. However, there are ways to automate the handling of Outlookattachments by using either VBA or VSTO.

In this article, I look intothe handling of Outlook attachments using VSTO 2008. The objective here is tocreate an AddIn for Outlook 2007 that will automatically handle incomingattachments.

Start by creating a new VSTO2008 AddIn for Outlook. You can do by accessing:
 
1.    File à New Project
2.    Project Types à Office 2007
3.    Outlook 2007 Add-in

You should name the Add-In andsave it. I chose the name OLSaveAttachmentAddIn. The image below shows the NewProject Window: 


Figure 1: Creating a new Outlook Add-in Project

You can now click OK tocontinue to the next step. The next step will show you the ThisAddIn.vb windowcontaining the basic code for the Add-In class.

Our next objective is to create:

1.    the events for handling the Outlook application and afew buttons from our popup menu;
2.    the popup object and buttons;
3.    a file system object that will point to the folderthat will hold the saved attachment

The three steps above are shownbelow. Comments are added to the code so that it can be better understood:

Public Class ThisAddIn
    'Outlook object
    Private WithEventsoOutlook As Outlook.Application

    'Popup button to toggle save attachment on and off
    Private WithEventsoBtnHandleSaveOnOff As Office.CommandBarButton

    'Popup button to handle showing the "About" form
    Private WithEventsoBtnHandleAbout As Office.CommandBarButton

    'Popup button to handle showing the "Help" folderinside Outlook explorer
    Private WithEventsoBtnHandleHelp As Office.CommandBarButton

    'Outlook explorer
    Private oActiveExplorer AsOutlook.Explorer

    'Commandbar Popup object to be added to Outlook's main menu
    Private oPopup AsOffice.CommandBarPopup

    'Commandbar button to be added to the popup object
    Private oBtn AsOffice.CommandBarButton

    'Commandbar button to be used across different buttons
    Private oBtnGeneral AsOffice.CommandBarButton

    'File System object
    Private oFileSystem AsObject

    'Form "About". You must add a form object
    Private ofrmAbout AsfrmAbout

    'String for the location of where the attachments will besaved
    Private ConststrconLocation As String= "C:\OLAttachments\"

    Private SubThisAddIn_Startup(ByVal sender As Object, _
                                  ByVal e AsSystem.EventArgs) Handles Me.Startup

        'Author         :Robert Martim
        'Purpose        : SetOutlook object, set active Outlook explorer, create menu and check toggle"Save Attachment" on/off
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        oOutlook =Application
        oActiveExplorer= oOutlook.ActiveExplorer
        CreateMenu()
        ToggleMe()
    End Sub

    Private SuboOutlook_NewMailEx(ByVal EntryIDCollection _
                                   As String) Handles Application.NewMailEx

        'Author         :Robert Martim
        'Macro Purpose  : Newe-mail event. Check for attachment and process attachment
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        Dim iFirst As Integer
        Dim strEntryID As String
        Dim iLength As Integer
        Dim oEmail AsOutlook.MailItem

        Try
            'Try for e-mail attachment and process attachment
            iFirst = 1
            iLength =Len(EntryIDCollection)
            strEntryID= Mid(EntryIDCollection, iFirst, (iLength - iFirst) + 1)

            oEmail =Application.Session.GetItemFromID(strEntryID)
            If HasAttachment(oEmail) ThenCall processAttachment(oEmail)
            oEmail = Nothing

        Catch ex As Exception
            'Catch error and report to user
           MsgBox(Err.Description, vbCritical, Err.Number)
        End Try
    End Sub

    Sub processAttachment(ByValolEmail As Outlook.MailItem)

        'Author         :Robert Martim
        'Macro Purpose  :Process attachment.
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        Dim oAttachment AsOutlook.Attachment
        Dim strNewName As String

        Try
            'Try:
            'Get registry value and see whether Save Attachment istoggled to True
            If getRegistry("ToggleSaveAttachment")= True Then
                'Check for sender whose attachment are to be saved
                If olEmail.SenderEmailAddress = "rm@msofficegurus.com" Then
                    'Create File System Object
                   oFileSystem = CreateObject("Scripting.FileSystemObject")
                    'Check if folder exists. If not, create destination folder

                    If NotoFileSystem.FolderExists(strconLocation) ThenoFileSystem.CreateFolder(strconLocation)
                    'Loop through attachments and save them
                    For Each oAttachment In olEmail.Attachments
                       'If file already exists in destination folder,warn user
                       If oFileSystem.FileExists(strconLocation& oAttachment.DisplayName) Then
                           If MsgBox("Thefile '" & oAttachment.DisplayName & _
                                      "' already exists. Do wish to use anothername?", _
                                vbQuestion +vbYesNo) = vbYes Then
                                strNewName =InputBox("Type the new file name with fileextension", _
                                    "New name...", oAttachment.DisplayName)
                               oAttachment.SaveAsFile(strconLocation & strNewName)
                           End If
                       Else
                           'Otherwise, just save it.
                            oAttachment.SaveAsFile(strconLocation& oAttachment.DisplayName)
                       End If
                    Next
                End If
               oFileSystem = Nothing
            End If
        Catch ex As Exception
            'Catch error and report to user
           MsgBox(Err.Description, MsgBoxStyle.Critical, Err.Number)
        End Try
    End Sub

    Function HasAttachment(ByValolEmail As Outlook.MailItem) As Boolean

        'Author         :Robert Martim
        'Purpose        :Function to determine if e-mail object has an attachment
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        If olEmail.Attachments.Count >= 1 Then HasAttachment = True
    End Function

    Sub CreateMenu()

        'Author         :Robert Martim
        'Purpose        :Create menu
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        Try
            ResetMenu()
            oPopup =oActiveExplorer.CommandBars("Menu Bar")_
               .Controls.Add(Office.MsoControlType.msoControlPopup, , , , Temporary:=True)
           oPopup.Caption = "Attachments"
            oBtn =oPopup.Controls.Add(Office.MsoControlType.msoControlButton)

            With oBtn
                .Caption = "SaveAttachment?"
                .Style= Office.MsoButtonStyle.msoButtonIconAndCaption
            End With

           oBtnHandleSaveOnOff = oBtn
           oBtnGeneral= oPopup.Controls.Add(Office.MsoControlType.msoControlButton)

            With oBtnGeneral
               .BeginGroup = True
               .Caption = "About"
               .FaceId= 326
               .Style= Office.MsoButtonStyle.msoButtonIconAndCaption
            End With

           oBtnHandleAbout = oBtnGeneral
           oBtnGeneral= oPopup.Controls.Add(Office.MsoControlType.msoControlButton)

            With oBtnGeneral
               .Caption = "Help"
                .FaceId= 984
                .Style= Office.MsoButtonStyle.msoButtonIconAndCaption
            End With

           oBtnHandleHelp = oBtnGeneral

        Catch ex As Exception
           MsgBox(Err.Description, MsgBoxStyle.Critical, Err.Number)
        End Try
    End Sub

    Sub ResetMenu()

        'Author         :Robert Martim
        'Purpose        :Reset the "Menu Bar"
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        On Error Resume Next
       oActiveExplorer.CommandBars.Item("MenuBar").Reset()
    End Sub

    Private SuboBtnHandleSaveOnOff_Click( _
                                            ByVal Ctrl As  _
                                           Microsoft.Office.Core.CommandBarButton, _
                                            ByRef CancelDefault AsBoolean) _
                                            Handles oBtnHandleSaveOnOff.Click

        'Author         :Robert Martim
        'Purpose        :Button event to handle toggling "Save Attachment" on/off
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        Try
            oFileSystem= CreateObject("Scripting.FileSystemObject")
            If NotoFileSystem.FolderExists(strconLocation) Then
               oFileSystem.CreateFolder(strconLocation)
                MsgBox("Your attachments will be saved in "& strconLocation, MsgBoxStyle.Information)
            End If
        Catch ex As Exception
            MsgBox("Ensure you have a folder named 'OLAttachments'located at C:\", MsgBoxStyle.Critical)
        End Try

        'Call sub to save toggle value in the Registry for lateruse

        Call saveRegistry("ToggleSaveAttachment",_
                         Not getRegistry("ToggleSaveAttachment"))

        'Call sub to toggle this button according to the savedvalue in the Registry
        Call ToggleMe()
        oFileSystem = Nothing
    End Sub

    Private SubToggleMe()

        'Author         :Robert Martim
        'Purpose        :Toggle "Save Attachment" on/off according to saved Registry value
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        Try
            If Not getRegistry("ToggleSaveAttachment") = True Then
               oBtn.State = Office.MsoButtonState.msoButtonUp
            Else
               oBtn.State = Office.MsoButtonState.msoButtonDown
            End If
        Catch ex As Exception
           MsgBox(Err.Description, MsgBoxStyle.Critical, Err.Number)
        End Try
    End Sub

    Function getRegistry(ByValstrKey As String)As Boolean

        'Author         :Robert Martim
        'Purpose        :Function to return the Registry saved value.
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        On Error Resume Next
        getRegistry =GetSetting("AddInProject", "AddInProjectValues", strKey)
        If Err.Number <> 0 ThengetRegistry = False
    End Function

    Sub saveRegistry(ByValstrKey As String,ByVal blnSetting AsBoolean)

        'Author         :Robert Martim
        'Purpose        : Functionto save the value to the Registry
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        On Error Resume Next
        SaveSetting("AddInProject", "AddInProjectValues",strKey, blnSetting)
    End Sub

    Private SuboBtnHandleAbout_Click(ByVal Ctrl As  _
                                     Microsoft.Office.Core.CommandBarButton, _
                                      ByRef CancelDefault AsBoolean) _
                                      Handles oBtnHandleAbout.Click

        'Author         :Robert Martim
        'Purpose        :Button event to handle showing the "About" form.
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        ofrmAbout = New frmAbout
       ofrmAbout.ShowDialog()
    End Sub


    Private SuboBtnHandleHelp_Click(ByVal Ctrl As  _
                                      Microsoft.Office.Core.CommandBarButton, _
                                       ByRef CancelDefault AsBoolean) _
                                       HandlesoBtnHandleHelp.Click

        'Author         :Robert Martim
        'Purpose        :Button to handle the "Help". In this case, MS Office Gurus website isopened inside and Outlook folder
        'Created on     : 18 April 2009
        'Last updated   : 18April 2009

        Dim oNS AsOutlook.NameSpace
        Dim oInboxFolder AsOutlook.Folder
        Dim oWebFolder AsOutlook.Folder

        oNS =oOutlook.GetNamespace("MAPI")
        oInboxFolder =oNS.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox)

        Try
            'Try to get the "Web Pages" folder
            oWebFolder= oInboxFolder.Folders("Web Pages")
        Catch ex As Exception
            'If the folder does not exist, add it
            oWebFolder= oInboxFolder.Folders.Add("Web Pages")
        End Try

        'Open MS Office Gurus URL in this folder
        With oWebFolder
           oWebFolder.WebViewURL = "http://msofficegurus.com/"
           oWebFolder.WebViewOn = True
           oActiveExplorer.CurrentFolder = oWebFolder
        End With

        oWebFolder = Nothing
        oInboxFolder = Nothing
        oNS = Nothing
    End Sub
End Class

You can now run the OutlookAdd-in. A new menu will be added to the Menu Bar as shown below: 

Figure 2: Custom menu added to the "Menu Bar"

The attachments will beautomatically (when the “Save Attachment?” button is toggled to ü) to the destination folder: 


Figure 3: Saved attachments

I’ve been working with Office and the new User Interface (UI) since the first beta was available to me. There is a common complaint about “how difficult” it is to get around the Ribbon, that it is less productive, that you need more clicks to get to a command, yadayadayada.

Is the Ribbon larger? Does it take more screen space? Well, get that measuring tape out of your toolbox and measure it by yourself and be surprised after comparing it to the old toolbars. Are the commands more difficult to find? Have a go in creating and modifying a chart and let me know.

I don’t have the statistics for all those who complain about the new UI, but from those who I have heard the complaints they all have one thing in common: they have used Office for a loooooooong time and had way too many customizations for which they will die before giving them up. As for the average user, it probably does not matter that much as they were using a fraction of Office resources and had little customizations of their own (I am saying this one without the stats to back it up, just guesswork).

But I am a guy who loves change. I see change as challenge, except for the middle part (“lle”), and the new UI represents just that: a challenge. And challenges are opportunities. It keeps us on the ball, active, and shrugs off any possible brain rheumatism we may be prone to.

This is how the RibbonX book came to life and I am happy we have had good feedback on it. It is a pity we left out Visual Studio, but at the time we thought it was not mature enough to justify a chapter on it. After VS 2008, I am convinced things will have changed drastically for the better and, the publishers willing; we will have a second edition which will be much richer in content.

If you have any queries or want to discuss things further, drop me a line.

As Teresa would say: life is full of opportunities, celebrate them all!

This article is the third part of the series on building custom toolbar in MS Access.

In this article I will show to execute actions and keyboard shortcuts using the OnAction property and the Execute method. Furthermore, I will show how to create shortcuts in Access.

The difference between OnAction and Execute is that OnAction executes a command that we determined through a procedure whereas Execute executes an internal command such as print. However, Execute is activated when we create the button, thus, we will use the button ID to execute the command. In other words, we will define the button such that it acts as if we had clicked on the Relationships button under the Tools menu.

Firstly, let us create the code that will build our menu:

Public Const MENUACCESS As String = "Menu Bar"

Sub executeMenus()    Dim mnu     As CommandBarPopup    Dim btn     As CommandBarButton        On Error Resume Next    CommandBars(MENUACCESS).Controls("Execute Commands").Delete    Set mnu = CommandBars(MENUACCESS).Controls.Add _     (Type:=msoControlPopup, before:=1)     mnu.Caption = "Execute Commands"    Set btn = mnu.Controls.Add(Type:=msoControlButton)     With btn      .Caption = "Example OnAction"      .FaceId = 1018      'Refers to the procedure to be executed      .OnAction = "Message"     End With    Set btn = mnu.Controls.Add(Type:=msoControlButton, ID:=523)     With btn      .Caption = "Example Execute"      .FaceId = 523      'This option is commented as we will use its ID to execute      'the command we want      '.Execute     End With End Sub

The new menu should look like this:

Figure 1 – OnAction property and Execute method

The FaceID choice is up to the reader.

Note that for the OnAction we put the name of the procedure to be executed when the button is clicked. However, on the Execute instance we refer to the command to be executed by using its ID. Given that the OnAction property needs a procedure, we shall use the example below as a simple way to get the button up-and-running:

Sub Message()    MsgBox "There is nothing under this button to be executed.", _     vbInformation End Sub

When we click the button the message below is shown to the user:

Figure 2 – MsgBox called by the OnAction property

As for the second button, when we click it, the Relationships window is open:

Figure 3 – Relationships windows opened through the Execute method

Once we have defined the ID of the command, the Execute method becomes redundant, as when we click on the button the command is executed. However, there may be situations when you wish to have the command executed immediately.

The above commands are executed through cliks, however, we can also add keyboard shortcuts. These shortcuts will require that you only press a few key combinations to execute the command you want.

This will be discussed in my next article.