Inserting a sound or beep when a cell increases by 1 digit.

Hi George,

Here is some code that demonstrates this for cell C5. The cell has to increase by 1, no more or less, to cause a beep.

This code uses two worksheet events, Change and Activate. Because of this, the code must be inserted in the worksheet's event code area. To do this, right-click on the worksheet's tab, select View Code, and paste the code below into the code pane that appears (it will be at the upper-right of the Visual Basic Editor (VBE) process window that appears).

Here's the code:

Dim C5value As Long

Private Sub Worksheet_Activate()
' Save current value of cell C5
C5value = [c5]
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, [c5]) Is Nothing Then
If Target.Value = C5value + 1 Then
Beep
End If
C5value = [c5]
End If
End Sub

Happy computing.

Damon

Damon, very interesting solution

I was wondering what the response to this would be. Thanks for sharing the knowledge, I've learned something new.

Regards,
Barrie

Re: Damon, very interesting solution

Barrie
With your newly found knowledge, what about coming up with code to do the same thing where C5's sheet is not activated under each of the following conditions :-
(1) C5 contains a formula which refers to another sheet, or
(2) C5's value is changed by another macro.
Brian

What if there is a formula in C5 that would make the cell increase by one digit? The code works if I manually insert a number but it doesn't work for a formula.

George

Re: Damon, very interesting solution

To do this just use the Applications Thisworkbook
object events and the Static statement.

eg

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Static C5value As Double

If Sheet1.[C5] = C5value + 1 Then
Beep
End If

C5value = Sheet1.[C5]

End Sub

So even if the cell is changed via formula
or macro it will beep

Ivan : I was wondering what the response to this would be. Thanks for sharing the knowledge, I've learned something new. : Regards,

Neat code Ivan.....

Although I would have enjoyed the challenge of trying to come up with a solution, I'm not sure I would have been able to do it.

Barrie :)