Compile Error: Ambiguous Name Worksheet_Change

Dear Forum,

I'm having a little difficulty with a piece of code that I'm trying to write. I keep getting a compile error when I test the code from the Visual Basic window in Excel. I think the problem lies in me having the same sub twice within a single worksheet module, but I may be wrong. Also, if you guys could look at the second set of code and make suggestions for how I can improve it I would be eternally grateful. What I'm trying to accomplish with the code is I have three data validation dependent drop down lists set up on a worksheet. What I would like to do is populate an adjacent cell with data based on the choices made in all three drop down lists. The choices have to match specific criteria exactly, and work so that after the user has made their third choice the adjacent cell is populated with the data corresponding to the choices they've made. Hope this helps and thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Intersect(Target, Range("q2:s1048576"))
If (Not Target Is Nothing) Then
Target.Offset(0, 1).Value = ""
End If

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)

If Range(q2).Value = "PLX" And Range(r2).Value = "Imaging_TopCall" And Range(s2).Value = "Missing From File" Then
PrelimRisk.AddItem = "Level 1"

ElseIf Range(q2).Value = "Document" And Range(r2).Value = "Assignment" And Range(s2).Value = "Not Needed" Then
PrelimRisk.AddItem = "Level 2"

ElseIf Range(q2).Value = "Document" And Range(r2).Value = "Assignment" And Range(s2).Value = "Incorrect Investor" Then
PrelimRisk.AddItem = "Level 1"

ElseIf Range(q2).Value = "PLX" And Range(r2).Value = "Inadequate Research" And Range(s2).Value = "Research Not Followed" Then
PrelimRisk.AddItem = "Level 1"

ElseIf Range(q2).Value = "Document" And Range(r2).Value = "Assignment" And Range(s2).Value = "Data Integrity Error" Then
PrelimRisk.AddItem = "Level 1"

End If

End Sub

You are right, you cannot do what you are trying to do so this is what you need:

Please Login or Register  to view this content.

Google Me
Find me located here Simon Lloyd and what i'm about Here
The above is NOT a link to a forum so is NOT against Rule 13

Lol, well spotted shg


Google Me
Find me located here Simon Lloyd and what i'm about Here
The above is NOT a link to a forum so is NOT against Rule 13

Thanks for all your help guys will try this out now and let you know if it works.

Hey guys I tried your suggestions and I got it to work, but now I have another problem. I'm getting a Runtime error 13 Type Mismatch. I modified the code slightly out of a necessity to expand the code to all the cells in the four columns my code is interacting with. Heres my code. The line that's in bold is the line that the compiler is higlighting as having an issue. Any help would be greatly appreciated. Thanks

Please Login or Register  to view this content.

Apparently the bold didn't work, but the line that causing the error is

Please Login or Register  to view this content.