I have combined the speech feature of Excel 2003 with a dropdown
selection to create a verbal help feature. By selecting a topic from
the dropdown, a file's user can get audio help when needed.
Unfortunately, unless the menu item is deleted immediately after the
audio message, it will repeat itself (as expected) each time there is
new input in the worksheet. Is there a macro that can delete the
dropdown selection soon after the message is played? Are there other
options? Thanks.

Michael

Thanks Charles

The dropdown I referred to is via Data Validation List. I don't
know if it's important, but the dropdown is comprised of merged
cells. If in the dropdown I select "Turning off the lights" from
the list, the macro reads the range's assigned value of 100 and
dictates the sentence "Be sure to turn off the lights." (It's a
silly example, I know.) What I need to happen-if possible-is for
the dropdown phrase to disappear once the speech is finished, so the
sentence is not read again the next time there is input in the
worksheet. My macro is as follows:

Private Sub Worksheet_Calculate()
With Application.Speech
Select Case Range("A1")
Case Is = 100: .Speak "Be sure to turn off the lights."
End Select
End With
End Sub

I am a novice at this, so thanks for bearing with me.

Michael

wrote:
Thanks Charles

The dropdown I referred to is via Data Validation List. I don't
know if it's important, but the dropdown is comprised of merged
cells. If in the dropdown I select "Turning off the lights" from
the list, the macro reads the range's assigned value of 100 and
dictates the sentence "Be sure to turn off the lights." (It's a
silly example, I know.) What I need to happen-if possible-is for
the dropdown phrase to disappear once the speech is finished, so the
sentence is not read again the next time there is input in the
worksheet. My macro is as follows:

Private Sub Worksheet_Calculate()
With Application.Speech
Select Case Range("A1")
Case Is = 100: .Speak "Be sure to turn off the lights."
End Select
End With
End Sub

I am a novice at this, so thanks for bearing with me.

Michael

Hi Michael,

That's a neat idea!

I managed to get it to work OK by changing to a WorksheetChange Event
Procedure...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
With Application.Speech
Select Case Range("A1")
Case 100
.Speak "Be sure to turn off the lights."
End Select
End With
End If
End Sub

For some unknown reason, with the equivalent in the WorksheetCalculate
Event Procedure...

Private Sub Worksheet_Calculate()
If Not Intersect(ActiveCell, Range("A1")) Is Nothing Then
With Application.Speech
Select Case Range("A1")
Case 100
.Speak "Be sure to turn off the lights."
End Select
End With
End If
End Sub

it was spoken twice when A1 was changed to 100???...


Ken Johnson

Ken,

Thanks for your input. I will be attempting your solution later today
to see if I can get it to work. It may be the reason it repeats itself
is because I have a number of dropdowns, but I shouldn't have thought
that was the problem. Please check back. I'll post the result ASAP.
Thanks again.

Michael

Apparently there are too many factors involved. I can't seem to get it
to work, other than to manually delete the contents of the dropdown
once the speech is finished. But that's alright. Thanks for your
input.

Michael

wrote:
Apparently there are too many factors involved. I can't seem to get it
to work, other than to manually delete the contents of the dropdown
once the speech is finished. But that's alright. Thanks for your
input.

Michael

Hi Michael,

Sorry to hear the result is not as you had hoped.
Feel free to email me a copy of the workbook for me to have a go at
fixing.

Ken Johnson