Embeded Wav

[Desert Piranha]

Hi All,

In Cell C9 is the result of a sum formula.

I have tried "Worksheet_Change(ByVal Target As Range)"
and "Worksheet_SelectionChange(ByVal Target As Range)'
Both of which don't update when C9 updates.

1- Using this code below works, but with a minor problem. If i delete a row or something like that then
the code runs, i assume cause the worksheet recalculates. A way to fix that?

2- Also i don't want the "little speaker shape" to be visible on the worksheet. Is there a better way than
the way i have below? It seems, it has to be visible in order to play.

3- A way to embed the wav in the workbook instead of the active sheet? IE: so it can be called from
multiple worksheets, but not go to another worksheet.

Thx for any direction.

Please Login or Register  to view this content.

[Norman Jones]

Hi Dave,

Try naming your cell - say: myCell - and replace your code with:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error Resume Next
Set rng = Intersect(Target, Me.Range("myCell").Precedents)
On Error GoTo 0

If Not rng Is Nothing Then
Call MyMacro
End If
End Sub
'<<=============


'=============>>
Sub MyMacro()
Dim SH As Worksheet

Set SH = ThisWorkbook.Sheets("Sheet3") '<<==== CHANGE
With SH.OLEObjects("Object 3")
.Visible = False
.Verb
End With
End Sub
'<<=============


---
Regards,
Norman


"Desert Piranha"
<[email protected]> wrote in
message news:[email protected]...
>
> Hi All,
>
> In Cell C9 is the result of a sum formula.
>
> I have tried "Worksheet_Change(ByVal Target As Range)"
> and "Worksheet_SelectionChange(ByVal Target As Range)'
> Both of which don't update when C9 updates.
>
> 1- Using this code below works, but with a minor problem. If i delete a
> row or something like that then
> the code runs, i assume cause the worksheet recalculates. A way to fix
> that?
>
> 2- Also i don't want the "little speaker shape" to be visible on the
> worksheet. Is there a better way than
> the way i have below? It seems, it has to be visible in order to play.
>
> 3- A way to embed the wav in the workbook instead of the active sheet?
> IE: so it can be called from
> multiple worksheets, but not go to another worksheet.
>
> Thx for any direction.
>
>
> Code:
> --------------------
> Private Sub Worksheet_Calculate()
> If Range("C9") > 100 Then
> Call MyMacro
> End If
> End Sub
>
> Sub MyMacro()
> ActiveSheet.Shapes("Object 1").Visible = True
>
> ActiveSheet.Shapes("Object 1").Select
> Selection.Verb Verb:=xlPrimary
>
> ActiveSheet.Shapes("Object 1").Visible = False
>
> Range("D1").Activate
>
> End Sub
> --------------------
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=550726
>

[Desert Piranha]

Hi Norman,

Good to hear from you.
Good luck in the World Cup.

The code you posted hides the shape great. However i can't seem to get
the "If Greater than 100" in C9 (myCell) to work.
My head is full of air this weekend.

Here is some of the stuff have tried:

Please Login or Register  to view this content.

Hi Dave,

Try naming your cell - say: myCell - and replace your code with:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error Resume Next
Set rng = Intersect(Target, Me.Range("myCell").Precedents)
On Error GoTo 0

If Not rng Is Nothing Then
Call MyMacro
End If
End Sub
'<<=============


'=============>>
Sub MyMacro()
Dim SH As Worksheet

Set SH = ThisWorkbook.Sheets("Sheet3") '<<==== CHANGE
With SH.OLEObjects("Object 3")
.Visible = False
.Verb
End With
End Sub
'<<=============


---
Regards,
Norman


"Desert Piranha"
<[email protected]> wrote in
message news:[email protected]...
>
> Hi All,
>
> In Cell C9 is the result of a sum formula.
>
> I have tried "Worksheet_Change(ByVal Target As Range)"
> and "Worksheet_SelectionChange(ByVal Target As Range)'
> Both of which don't update when C9 updates.
>
> 1- Using this code below works, but with a minor problem. If i delete a
> row or something like that then
> the code runs, i assume cause the worksheet recalculates. A way to fix
> that?
>
> 2- Also i don't want the "little speaker shape" to be visible on the
> worksheet. Is there a better way than
> the way i have below? It seems, it has to be visible in order to play.
>
> 3- A way to embed the wav in the workbook instead of the active sheet?
> IE: so it can be called from
> multiple worksheets, but not go to another worksheet.
>
> Thx for any direction.
>
>
> Code:
> --------------------
> Private Sub Worksheet_Calculate()
> If Range("C9") > 100 Then
> Call MyMacro
> End If
> End Sub
>
> Sub MyMacro()
> ActiveSheet.Shapes("Object 1").Visible = True
>
> ActiveSheet.Shapes("Object 1").Select
> Selection.Verb Verb:=xlPrimary
>
> ActiveSheet.Shapes("Object 1").Visible = False
>
> Range("D1").Activate
>
> End Sub
> --------------------
>
>
> --
> Desert Piranha
>
>
> ------------------------------------------------------------------------
> Desert Piranha's Profile:
> http://www.excelforum.com/member.php...o&userid=28934
> View this thread: http://www.excelforum.com/showthread...hreadid=550726
>

[Norman Jones]

Hi Dave,

Sorry, I overlooked the condition!

Try:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error Resume Next
Set rng = Intersect(Target, Me.Range("myCell").Precedents)
On Error GoTo 0

If Not rng Is Nothing And Range("Mycell").Value > 100 Then
Call MyMacro
End If
End Sub
'<<=============

> Good luck in the World Cup.

And to you!!


---
Regards,
Norman

[Desert Piranha]

Hi All,

For those following the thread. Due to a really strange active cell condition,
after the code was ran. Norman has updated it. Here is the code in its entirety.

Please Login or Register  to view this content.

Thx much Norman,
Dave

Hi Dave,

Sorry, I overlooked the condition!

Try:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error Resume Next
Set rng = Intersect(Target, Me.Range("myCell").Precedents)
On Error GoTo 0

If Not rng Is Nothing And Range("Mycell").Value > 100 Then
Call MyMacro
End If
End Sub
'<<=============

> Good luck in the World Cup.

And to you!!


---
Regards,
Norman