Compile error: ambiguous name detected vba excel

Question

Hello,

i'm trying to have some hyperlinked images on a Worksheet which can only be seen on the case if the referenced cells value is negative. this image will then appear and hyperlink to somewhere else.

i managed to successful do that with a couple of cells with image "Picture 1" and it's working perfectly. the issue happens when i try to add another image to the worksheet "Picture 2" and assign it to different cells. somehow the code doesn't work.

here is the code i'm using:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("K8")) Is Nothing Then

    If WorksheetFunction.CountIf(Range("M8"), "<0") Then

        Me.Shapes("Picture 1").Visible = msoTrue

    Else

        Me.Shapes("Picture 1").Visible = msoFalse

    End If

End If

End Sub

---

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("K14")) Is Nothing Then

    If WorksheetFunction.CountIf(Range("M14"), "<0") Then

        Me.Shapes("Picture 2").Visible = msoTrue

    Else

        Me.Shapes("Picture 2").Visible = msoFalse

    End If

End If

End Sub

here is a screenshot from the error. can someone help?

Answer 1

When I made a change in the third sheet, then it updates the first sheet and you get the error because the activesheet is the third sheet.  If you make this change to your code it works:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("D5")) Is Nothing Then

        macro1

    End If

    If Not Intersect(Target, Range("D7")) Is Nothing Then

            macro2

    End If

    If Not Intersect(Target, Range("D9")) Is Nothing Then

            macro3

    End If

End Sub

Sub macro1()

If WorksheetFunction.CountIf(Range("E5"), "<0") Then

        Me.Shapes("Picture 1").Visible = msoTrue

    Else

        Me.Shapes("Picture 1").Visible = msoFalse

    End If

End Sub

Sub macro2()

If WorksheetFunction.CountIf(Range("E7"), "<0") Then

        Me.Shapes("Picture 2").Visible = msoTrue

    Else

        Me.Shapes("Picture 2").Visible = msoFalse

    End If

End Sub

Sub macro3()

If WorksheetFunction.CountIf(Range("E9"), "<0") Then

        Me.Shapes("Picture 3").Visible = msoTrue

    Else

        Me.Shapes("Picture 3").Visible = msoFalse

    End If

End Sub

the predefined object Me is appropriate in code in a sheet module and it represents the sheet that contains the code.  This is the proper qualification for those shapes since they are on the first sheet and the code is in the first sheet module.

You could explicitly qualify them with code like this 

worksheets("Sales").Shapes("Picture 2").Visible = msoTrue

rather than use Me  

--

Regards,

Tom Ogilvy

Answer 2

Three problems. . . . .at least<g>

1.  Take Macro1, 2, 3 out of Sales Sheet Module and place in a General Module.  Leave sheet

modules for sheet event code.

2.  You have a second unrelated Macro1 and some event code in InputData Sheet Module

Move that macro to a General module and rename it to something other than Macro1

Or just dump that if you don't need it.

3.  There is no sign at all of any code for Picture 7 which is OK because there is no Picture 7 in the

workbook you posted.

I have uploaded the workbook to this site. . . . . .

https://onedrive.live.com/?cid=863B7DD7364138EC&id=863b7dd7364138ec!113

Or just stick with Tom who would be my first choice when looking for VBA assistance.

Gord

Answer 3

Can't have two events of same type in same worksheet.

Try something like this. . . . .

Place the event code in the worksheet module and macro1 and macro2 in a regular module.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("K8")) Is Nothing Then

        macro1

    Else

        If Not Intersect(Target, Range("K14")) Is Nothing Then

            macro2

        End If

    End If

End Sub

Sub macro1()

If WorksheetFunction.CountIf(Range("M8"), "<0") Then

        ActiveSheet.Shapes("Picture 1").Visible = msoTrue

    Else

        ActiveSheet.Shapes("Picture 1").Visible = msoFalse

    End If

End Sub

Sub macro2()

    If WorksheetFunction.CountIf(Range("M14"), "<0") Then

        ActiveSheet.Shapes("Picture 2").Visible = msoTrue

    Else

        ActiveSheet.Shapes("Picture 2").Visible = msoFalse

    End If

End Sub

Gord

Answer 4

ok, that code worked perfect with two images. however, i tried to apply the code on one more picture and different cells but i got an error.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("K8")) Is Nothing Then

        macro1

    Else

        If Not Intersect(Target, Range("K14")) Is Nothing Then

            macro2

    Else

        If Not Intersect(Target, Range("P8")) Is Nothing Then

            macro3

        End If

    End If

End Sub

Sub macro1()

If WorksheetFunction.CountIf(Range("M8"), "<0") Then

        ActiveSheet.Shapes("Picture 1").Visible = msoTrue

    Else

        ActiveSheet.Shapes("Picture 1").Visible = msoFalse

    End If

End Sub

Sub macro2()

    If WorksheetFunction.CountIf(Range("M14"), "<0") Then

        ActiveSheet.Shapes("Picture 2").Visible = msoTrue

    Else

        ActiveSheet.Shapes("Picture 2").Visible = msoFalse

    End If

End Sub

Sub macro3()

    If WorksheetFunction.CountIf(Range("R8"), "<0") Then

        ActiveSheet.Shapes("Picture 3").Visible = msoTrue

    Else

        ActiveSheet.Shapes("Picture 3").Visible = msoFalse

    End If

End Sub

Answer 5

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("K8")) Is Nothing Then

        macro1

    ElseIf Not Intersect(Target, Range("K14")) Is Nothing Then

            macro2

    ElseIf Not Intersect(Target, Range("P8")) Is Nothing Then

            macro3

    End If

End Sub

--

Regards,

Tom Ogilvy