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

Try spelling marco1 as macro1 instead.

Gord

Answer 4

ok, i feel stupid :D. i fixed that but i hit another error. this error happens when i try to input the date in another sheet cells which will then use that last code to reflect on "Sales" sheet.

so to go over the steps again:

1- main sheet "Sales" will have a few cells like this

Aug	Sep	MOM
4	6	-30.3%
6	8	-15.2%
4	5	-20.0%
3	3	-23.3%

2- the mages that i asked about in the beginning will only appear in case if raw 1 MOM for example is negative.

3- and that rule will be applied on other cells with other images.

4- we have another sheet which will be used to input Sep data and it will reflect automatically on "Sales" sheet.

Answer 5

Hard to keep track of your changes.

Are you sure that Picture 7 is a shape on the ActiveSheet

Suggest you could upload your workbook to a file-sharing site then post the URL of that site.

Gord