Macro to select last cell in row and start Text to Speech

[JMDR]

I would like to have a macro (that I can assign to a button) to select the last cell in the row of the currently selected cell (there may or may not be blank cells in the row), then activate the Text to Speech feature to read the contents of the cell, and then have it continue to the next row down until it comes to the end of the column (or until stopped by the user).

Why? I'm creating a data entry template, and the last cell in each row has a concatenation formula to read back the data in an intelligible manner. I want to lock the worksheet to prevent any changes other than entering validated data into the necessary cells. I also want to make it very obvious (i.e. a big button to press) for the people entering the data to activate the Speech feature.

I'm pretty experienced using Excel, but less so VBA (but I don't mind tweaking the code). I've tried a bunch of things myself, but have not had any success so far. Any tips in the right direction would be appreciated.

(Excel 2010, in Windows 7)

 

[BobUmlas]

Sub tts()
Cells(ActiveCell.Row, 16384).End(xlToLeft).Select
Doit:
Application.Speech.Speak Selection.Value
Selection.Offset(1).Select
If Len(Selection.Value) > 0 Then GoTo Doit
End Sub

 

[JMDR]

Awesome, worked perfect. Many thanks.

The one little "bug" is that if I press [Esc] to stop the text-to-speech, Excel throws a VBA run-time error '287' and the debugger (which I think will scare my data enter-ers ). Is there a way to prevent that from happening?

 

[jim may]

THis worked for me...

Sub tts()
On Error GoTo ErrHandler
 Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Select
Doit:

 Application.Speech.Speak Selection.Value
 Selection.Offset(1).Select
 Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Select
 If Len(Selection.Value) > 0 Then GoTo Doit
Exit Sub
ErrHandler:
 Application.SendKeys "^{Break}"
 End Sub

 

[JMDR]

Perfect. Beautiful. Thank you both for your help!