Activate speech when sheet is changed

[faureman via OfficeKB.com]

I have a sheet that is "monitoring" the status of a another sheet. When the
source sheet changes, the second sheet - named "Error"- displays a message
using a simple "If" statement.

Normally, the monitoring sheet is displaying an empty string (no message)
until the source sheet activates an error condition (0 changes to 1 for error
message activation) within the cells being monitored. When this occurs, a
message is displayed in the error sheet.

Now, I want to activate the speech functionality in "column A" over a fixed
range of cells in the error sheet so the message will be annunciated. How can
I accomplish this?

 

[Guest]

This routine monitors cells A1 thru A10 which are normally blank (an IF does
this)

If a calculation causes one or more of these cells to become non-blank, the
warning is issued.

Private Sub Worksheet_Calculate()
Set monitor = Range("A1:A10")
For Each r In monitor
If r.Value <> "" Then
Application.Speech.Speak "danger will robinson danger"
End If
Next
End Sub

The warning will be re-issued on each calculation until the problem is
fixed. Note this is Event code that goes in the worksheet code area, not a
standard module.

 

[marrowm]

I have a sheet that is "monitoring" the status of a another sheet. When the
source sheet changes, the second sheet - named "Error"- displays a message
using a simple "If" statement.

Normally, the monitoring sheet is displaying an empty string (no message)
until the source sheet activates an error condition (0 changes to 1 for error
message activation) within the cells being monitored. When this occurs, a
message is displayed in the error sheet.

Now, I want to activate the speech functionality in "column A" over a fixed
range of cells in the error sheet so the message will be annunciated. How can
I accomplish this?

I have no clue about how efficient this needs to be for you... and I
am not convinced I understand your question 100%..

But if I have a workbook with two worksheets - Data and Error where
cell A1 on the Error worksheet has an error message that is based on
an IF statement checking values in the Data worksheet, I can put code
in the SheetChange macro as follows:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Worksheets("Error").Range("A1").Speak
End Sub

Now, every time you change anything in the workbook, the error message
is read out. That's a good starting point I think?

UKMatt