Let us suppose that you have a list. In this list, you want to identify the cells that contain a certain text. Figure 1 shows the initial setup of our problem:

Figure 1 - Conditionally formatting based on text in a cell

What we plan to do is to identify the products that contain the word “used” (for now). We show you two different ways to get this done.

In the first example, we type in the word directly into the formula. In the second example, we do this by using a validation list from where you can choose what to look for and then the CF is applied.

To begin with, choose the area containing the data (starting from the top-most cell). Add a new rule and choose the option Use a formula to determine which cells to format. In the formula box, you must enter the following formula: =IF(NOT(ISERROR(FIND("used",A1,1))),1,0)=1. Choose the formatting of your liking. Figure 2 shows the step visually:

Figure 2 - Entering the conditional formatting formula

Obviously that, as it is, the conditional formatting is pretty much useless to us. What if we want to format whenever the word “new” comes up? Or when “repaired” comes up? In theses case it does not take a genious to figure out that we’re in serious trouble. It is simply not practical keep changing the value around everytime we need to conditionally format based on a word found in the text.

The solution is to implement a truly functional formula. In this case, we can use a data validation list to choose from (we discuss data validation in details in the second part of this eBooklet).

Here are the steps:

·         Type in the word “repaired, used and new” in the range F1:F3;
·         Select cell D1 where the data validation will be inserted;
·         Open the data validation tool (Data tab à Data tools group à Data Validation splitbutton à Data validation button);
·         From the Allow dropdown list, choose List;
·         In the Source field, choose the range F1:F3;
·         Click OK to finish.

Now, when you click on D1, you will get the validated list as shown in Figure 3:


Figure 3 - Validation list to be used in our conditional formatting

Now, all you need to do is to change the original formula to point to cell D1 as shown in Figure 4:


Figure 4 - Edited formula

If you are wondering above the “=1” at the end, we only added this to make it explicit that the formula is evaluating to 1 (true) so that the conditional formatting can be applied. There is not need to have the “=1” at the end of the formula. It is entirely up to you.

You can now select from the validated list any of the values and see your conditional formatting at work as shown in Figure 5:

Figure 5 - Final step o four conditional formatting with data validation


You may also want to read this article (http://www.excelguru.ca/blog/2009/04/23/making-an-icon-set-show-only-two-conditions/) from my fellow MVP, Ken Puls.

Here's an interesting subject: worksheet rename event. As we all know, Excel comes with various built-in events such as Worksheet_BeforeDoubleClick, Worksheet_SelectionChange, Worksheet_Change, etc. However, we do not have an event to handle the renaming of a worksheet, something like Worksheet_AfterRename, or Worksheet_Rename.

This week I got a question about a worksheet rename event. Here's my first attempt at handling this event (place the code in ThisWorkbook module):

Private mstr_ActiveSheetPreviousName    As String
Private lng_DeactivatedSheetIndex       As Long
Private mstr_ActiveSheetName            As String

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    lng_DeactivatedSheetIndex = Sh.Index
    SheetNameChange 0
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    SheetNameChange 1
End Sub

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

Sub SheetNameChange(ByVal lngCaller As Long)
    Select Case lngCaller
        Case 0
            If lng_DeactivatedSheetIndex <> ThisWorkbook.ActiveSheet.Index Then
                If ThisWorkbook.Sheets(lng_DeactivatedSheetIndex).Name <> mstr_ActiveSheetPreviousName Then
                    NameChanged mstr_ActiveSheetPreviousName, ThisWorkbook.Sheets(lng_DeactivatedSheetIndex).Name
                    mstr_ActiveSheetPreviousName = mstr_ActiveSheetName
                End If
            End If
        Case 1
            mstr_ActiveSheetPreviousName = ThisWorkbook.ActiveSheet.Name
            mstr_ActiveSheetName = ThisWorkbook.ActiveSheet.Name
        Case 2
            mstr_ActiveSheetName = ThisWorkbook.ActiveSheet.Name
            If mstr_ActiveSheetName <> mstr_ActiveSheetPreviousName Then
                NameChanged mstr_ActiveSheetPreviousName, mstr_ActiveSheetName
                mstr_ActiveSheetPreviousName = mstr_ActiveSheetName
            End If
    End Select
End Sub

Sub NameChanged(ByVal strOldName As String, ByVal strNewName As String)
    MsgBox "The worksheet name changed from '" & strOldName & "' to '" & strNewName
End Sub

I have given too thought to it, so it is really just an attempt. If any of you have a better solution or wants to make suggestion, please drop me a line either by e-mail or through a comment.

First of all, what is a Smart Tag? “Smart Tags” is a feature of Microsoft Office that recognizes certain kind of text (such as a date) and then offer the users a list of possible actions related to that text.

Excel already comes with a plethora of Smart Tags, but we can also implement our own list of Smart Tags. When Smart Tag is active, when you hover the mouse cursor over the word a little button is displayed. Clicking on it will open the Smart Tag Actions.

You can turn on/off Smart Tags at:

1.    Office Button
2.    Excel Options
3.    Proofing à AutoCorrect Options
4.    Smart Tags

The image below shows the Smart Tag in action when Microsoft’s financial symbol is typed in a cell:


Figure 1: Smart Tag at work

Let us start by creating a new Excel project as follows: 

1.    File à New Project
2.    Project Types à Office 2007
3.    Excel 2007 Workbook (or Add-in, if you prefer an Excel Add-in)

The first thing we will do is to check whether Smart Tags are active in Excel. You should add the following code to the workbook’s Startup event (notice that below I also add the event for the click on the Smart Tag button we will create later. The code must come after Thisworkbook class):

    WithEvents MySmartTagClickAction As _
                       Microsoft.Office.Tools.Excel.Action

    Private Sub ThisWorkbook_Startup(ByVal sender As Object, _
                                     ByVal e As System.EventArgs) _
                                     Handles Me.Startup
        If Application.AutoCorrect.DisplayAutoCorrectOptions = False Then
            Application.AutoCorrect.DisplayAutoCorrectOptions = True
            Me.SmartTagOptions.DisplaySmartTags = _
                Excel.XlSmartTagDisplayMode.xlIndicatorAndButton
            MsgBox("Smart Tags have been enabled...", _
                   MsgBoxStyle.Information, "Smart Tags Enabled...")
        End If
        BuildSmartTags()
    End Sub

When the code is run, if Smart Tags are disabled, they will be enabled and a message will be shown:

Figure 2: Enabling Smart Tags via code

Now create a Smart Tag list in one of your worksheets. You can later change its visibility and hide it away from the user (Home Tab à Cells à Format à Hide & Unhide à Hide Sheet):


Figure 3: Smart Tags list

As you will remember from the Startup code, we had a call to a procedure named BuildSmartTags. This is our next objective:

    Sub BuildSmartTags()
        'Smart Tag object. Define the namespace and the identifier.
        'Here I use the namespace as my web address and identifier as
        'My Smart Tag
        Dim oMySmartTags As New  _
            Microsoft.Office.Tools.Excel.SmartTag( _
            "www.msofficegurus.com/MySmartTags#SmartTags", _
            "My Smart Tag")

        'Worksheet object that holds the list of Smart Tags
        Dim oSmartTagListWorksheet As Excel.Worksheet

        'Counter to loop through Smart Tag list
        Dim lngCount As Long

        'Smart Tag string stored in the worksheet
        'to be added as the Smart Tag Term
        Dim strSmartTagTerm As String

        'Set initial counter equal to 1
        lngCount = 1

        'Set worksheet that contains the Smart Tag list
        oSmartTagListWorksheet = Me.Sheets("Smart Tags List")

        'Set the first Smart Tag term
        strSmartTagTerm = _
            oSmartTagListWorksheet.Range("A" & lngCount).Value

        'Loop through Smart Tag Term List
        Do Until Len(strSmartTagTerm) = 0
            oMySmartTags.Terms.Add(strSmartTagTerm)
            lngCount = lngCount + 1
            strSmartTagTerm = _
              oSmartTagListWorksheet.Range("A" & lngCount).Value
        Loop

        'New Action button to be added to Smart Tag Action list
        'The name in parentheses is the name of the button
        MySmartTagClickAction = New  _
            Microsoft.Office.Tools.Excel.Action( _
            "Go to MS Office Gurus online")

        'Associate the button above with the
        'Smart Tag object (oMySmartTags)
        'that we declared above
        oMySmartTags.Actions = New  _
            Microsoft.Office.Tools.Excel.Action() _
               {MySmartTagClickAction}

        'Add the Smart Tag object (oMySmartTags)
        'the VstoSmartTags collection
        Me.VstoSmartTags.Add(oMySmartTags)
    End Sub

The final piece of the jigsaw is the code to handle the click event:

    'Event that handles the action click
    Private Sub MySmartTagClickAction_Click( _
        ByVal sender As Object, _
        ByVal e As Microsoft.Office.Tools.Excel.ActionEventArgs) _
        Handles MySmartTagClickAction.Click

        Me.FollowHyperlink("http://www.msofficegurus.com", _
           , True, True)
    End Sub

You can now try the code out and see who it works as you type any word from your list:


Figure 4: Custom Smart Tag in action

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