A macro that can delete the contents of a cell after a delayed interval

[mrlanier]

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

 

[mrlanier]

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

 

[Ken Johnson]

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

 

[mrlanier]

I think the reason it spoke twice was because anytime you take an
action other than simply clicking on a cell, it will cause the macro to
execute. This is why I'm trying to figure out how to cause the
dropdown to self-delete. That's the only thing I can think to do.
Hopefully, better brains than mine can figure it out. Thanks.

Michael

 

[Ken Johnson]

I think the reason it spoke twice was because anytime you take an
action other than simply clicking on a cell, it will cause the macro to
execute. This is why I'm trying to figure out how to cause the
dropdown to self-delete. That's the only thing I can think to do.
Hopefully, better brains than mine can figure it out. Thanks.

Michael

Hi Michael,

Are you unable to use the WorksheetChange solution?

Ken Johnson

 

[Ken Johnson]

I think the reason it spoke twice was because anytime you take an
action other than simply clicking on a cell, it will cause the macro to
execute. This is why I'm trying to figure out how to cause the
dropdown to self-delete. That's the only thing I can think to do.
Hopefully, better brains than mine can figure it out. Thanks.

Michael

Hi Michael,

This seems to have stopped the stutter with WorksheetCalculate...

Private Sub Worksheet_Calculate()
If Not Intersect(ActiveCell, Range("A1")) Is Nothing Then
Application.EnableEvents = False
On Error GoTo ERRHANDLER
With Application.Speech
Select Case Range("A1")
Case Is = 100: .Speak "Be sure to turn off the lights."
Range("A1").ClearContents
End Select
End With
End If
ERRHANDLER: Application.EnableEvents = True
End Sub

It's curious that the A1 cell needs to be cleared of the 100 to stop
the repeat, I can't see why it should repeat.

Ken Johnson

 

[mrlanier]

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

 

[mrlanier]

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

 

[Ken Johnson]

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