Dashboard Reporting

highlander2536

New Member
Joined
Feb 7, 2012
Messages
4
I have created an excel dashboard that contains two separate traffic light charts. Each traffic light collects its data from a different cell. When only 1 traffic light is showing and the VB code is for 1 light it works. When I add the second light and code I get a "Compile Error: Ambiguous name detected: Worksheet_Change"

The code is linked to the worksheet as an excel object.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
       
    If Not Intersect(Target, Range("V1")) Is Nothing Then
        If IsNumeric(Target.Value) Then
            If Target.Value < 55 Then
                ActiveSheet.Shapes("Oval 10").Fill.ForeColor.RGB = vbRed
                ActiveSheet.Shapes("Oval 11").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 12").Fill.ForeColor.RGB = vbBlack
            Else
            If Target.Value >= 55 And Target.Value < 60 Then
                ActiveSheet.Shapes("Oval 10").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 11").Fill.ForeColor.RGB = vbYellow
                ActiveSheet.Shapes("Oval 12").Fill.ForeColor.RGB = vbBlack
            Else
            If Target.Value >= 60 Then
                ActiveSheet.Shapes("Oval 10").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 11").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 12").Fill.ForeColor.RGB = vbGreen
            End If
        End If
    End If
    End If
    End If
End Sub
-----------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
       
    If Not Intersect(Target, Range("V24")) Is Nothing Then
        If IsNumeric(Target.Value) Then
            If Target.Value < 55 Then
                ActiveSheet.Shapes("Oval 8").Fill.ForeColor.RGB = vbRed
                ActiveSheet.Shapes("Oval 13").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 14").Fill.ForeColor.RGB = vbBlack
            Else
            If Target.Value >= 55 And Target.Value < 60 Then
                ActiveSheet.Shapes("Oval 8").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 13").Fill.ForeColor.RGB = vbYellow
                ActiveSheet.Shapes("Oval 14").Fill.ForeColor.RGB = vbBlack
            Else
            If Target.Value >= 60 Then
                ActiveSheet.Shapes("Oval 8").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 13").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 14").Fill.ForeColor.RGB = vbGreen
            End If
        End If
    End If
    End If
    End If
End Sub

Is there any way to run both codes without the error?

Thanks
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
You'll need to combine them like this

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
       
    If Not Intersect(Target, Range("V1")) Is Nothing Then
        If IsNumeric(Target.Value) Then
            If Target.Value < 55 Then
                ActiveSheet.Shapes("Oval 10").Fill.ForeColor.RGB = vbRed
                ActiveSheet.Shapes("Oval 11").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 12").Fill.ForeColor.RGB = vbBlack
            Else
            If Target.Value >= 55 And Target.Value < 60 Then
                ActiveSheet.Shapes("Oval 10").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 11").Fill.ForeColor.RGB = vbYellow
                ActiveSheet.Shapes("Oval 12").Fill.ForeColor.RGB = vbBlack
            Else
            If Target.Value >= 60 Then
                ActiveSheet.Shapes("Oval 10").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 11").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 12").Fill.ForeColor.RGB = vbGreen
            End If
        End If
    End If
    End If
       
    ElseIf Not Intersect(Target, Range("V24")) Is Nothing Then
        If IsNumeric(Target.Value) Then
            If Target.Value < 55 Then
                ActiveSheet.Shapes("Oval 8").Fill.ForeColor.RGB = vbRed
                ActiveSheet.Shapes("Oval 13").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 14").Fill.ForeColor.RGB = vbBlack
            Else
            If Target.Value >= 55 And Target.Value < 60 Then
                ActiveSheet.Shapes("Oval 8").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 13").Fill.ForeColor.RGB = vbYellow
                ActiveSheet.Shapes("Oval 14").Fill.ForeColor.RGB = vbBlack
            Else
            If Target.Value >= 60 Then
                ActiveSheet.Shapes("Oval 8").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 13").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 14").Fill.ForeColor.RGB = vbGreen
            End If
        End If
    End If
    End If
    End If
End Sub
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,696
Office Version
  1. 365
Platform
  1. Windows
No, you need to combine them into a single procedure of Private Sub Worksheet_Change, I think because Worksheet_Change is specifically reserved for identifying and triggering events relating to a change in the sheet you're working on, the code can't differentiate between two identical names that trigger the same event
 

Watch MrExcel Video

Forum statistics

Threads
1,122,632
Messages
5,597,287
Members
414,134
Latest member
Tiyas44

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top