Edit my Macro Please

[Gerard Sanchez]

'Hi,

'Currently I am using the function below to and called on whenever there is
a discrepancy between two values on an IF formula.

Function beepNow()
Beep
End Function

'I was wondering if there is a way for me to do 2 beeps aside from the 1
beep above I can call whenever certain cells changes its value.
This would be applied to the whole workbook.

'Any input would be very much appreciated!

 

[JLGWhiz]

Here is one that I have used:

Sub MultiBeep(NumBeeps)
For Counter = 1 To NumBeeps
Beep
Run "BeepTime"
Next Counter
End Sub

You use it like a function. If you want 2 beeps then:

Sub test()
MultiBeep 2
End Sub

For three beeps

Sub test2()
MultiBeep 3
End Sub

 

[JLGWhiz]

Delete the Run BeepTime line. It refers to a delay timer that is not
included in the code I gave you.

 

[Dave Peterson]

JLGWhiz seems to have lost the "beepTime" subroutine.

Here's an alternative:

Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub MultiBeep(Optional NumBeeps As Long = 1)
Dim Counter As Long
For Counter = 1 To NumBeeps
Beep
Sleep 250 'quarter of a second
Next Counter
End Sub
Sub test2()
Call MultiBeep(2)
End Sub

(With just a few changes to be irritating <vbg>.)

 

[JLGWhiz]

You will probably need a delay to distinguish the number of beeps, so here is
the modified sub to do that.

Sub MultiBeep(NumBeeps)
For counter = 1 To NumBeeps
Beep
s = Timer + 0.5
Do While Timer < s
DoEvents
Loop
Next counter
End Sub

 

[Dave Peterson]

And depending on the version of excel you're using and how it was installed:

Option Explicit
Sub testme()
Application.Speech.Speak "Beep, beep"
End Sub

 

[Dave Peterson]

And to be really irritating:

Option Explicit
Sub test2()
With Application
.Speech.Speak .Rept("beep", 3)
End With
End Sub

 

[Gerard Sanchez]

Question on

How to I call this, say when A1=A2?

Thanks

 

[Gerard Sanchez]

Maybe Excel talking would sound funny . . .lol!

 

[Dave Peterson]

Are A1 and A2 both changed by typing?

If yes, then you could use the worksheet_Change event.

If either A1 or A2 are changed by a formula, then you could use the
worksheet_Calculate event.

But as a user, this would drive me nuts. I'd much rather see an adjacent cell
formatted in big bold letters with a formula like:

=if(a1<>a2,"","Please make them different!"