J-Walk & Associates, Inc.

Simple Ribbon Example Posted

I posted a new Excel 2007 tip today: Add The Speech Controls To The Ribbon.

It describes an add-in ( available for download) that creates a new group on the Review tab of the ribbon. This group contains the five text-to-speech controls that -- for some reason -- were omitted from the UI. In addition, the ribbon group contains a custom button that displays the Speech Properties dialog box. This dialog box lets you choose a voice and set the speed.

Writing the RibbonX code was straightforward, but it still took me many save-and-test cycles before I got it right. Here's what it looks like:

For the record, I had to consult my Excel 2007 Power Programming With VBA book several times when working on this project.

The most difficult part was writing the VBA code to display the Speech Properties dialog. This simple task required about 70 lines of code, including four API functions. As it turns out the sapi.cpl file is not located in the same place as the other Control Panel apps. Therefore, you need to consult the registry to find the location of the file. At least that's the conclusion I came to. If there's a simpler way, please let me know.

- Reader Comments -

Following are comments in response to this item.
The most recent comment is at the bottom.

1. By Modeste GeeDee. Comment posted 22 August, 2008 3:06am

   Hi John,
   
   I don't use Excel 2007...
   But for a long time i'm used to this code
   ;o)))
   
   In module ThisWorkbook :
   Private Sub Workbook_Open()
   On Error Resume Next
   ' Add Microsoft Speech object library
   ActiveWorkbook.VBProject.References.AddFromGuid "{C866CA3A-32F7-11D2-9602-00C04F8EE628}", 5, 0
   DoEvents
   Test
   End Sub
   
   in a standard module :
   Option Explicit
   Public Vocal As New SpVoice
   
   Sub Talk(phrase As Variant) '
   Vocal.Speak phrase
   End Sub
   
   Sub Test()
   Dim nbr As Integer, i As Integer
   nbr = 10
   Talk "I'm now counting till " & nbr
   For i = 1 To nbr
   Talk i
   Next
   Talk "Finished counting to " & i - 1
   End Sub
   
   in Sheet Module :
   Option Explicit
   
   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim UCell As Range
   If Target.Cells.Count > 1 Then
   For Each UCell In Target
   If Not IsEmpty(UCell) Then
   Talk "Range " & UCell.Address(0, 0) & " " & UCell.Formula
   End If
   Next
   Else
   Talk Target.Formula
   ' Talk Target.Text
   End If
   End Sub
2. By Ron de Bruin. Comment posted 22 August, 2008 9:04am

   Hi John
   
   I add this page to my site last week.
   Maybe useful for your readers if they also want to add other commands
   
   http://www.rondebruin.nl/notinribbon.htm